Insert Rows

Mrfunpantz · #1 (**permalink**) 03-25-04, 11:45

Hi I am trying to take a table of Data, then after each change in Column "D" I want to insert 3 blank Rows. Is there anyway to do this? Thanks!

Smitty · #2 (**permalink**) 03-25-04, 11:56

Welcome to the Board!

Try this:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
     '   only look at single cell changes
    If Target.Count > 1 Then Exit Sub
    Set rng = Columns("D:D")
    '   only look at that range
    If Intersect(Target, rng) Is Nothing Then Exit Sub
    With rng
        Range(Target.Offset(1, 0), Target.Offset(3, 0)).EntireRow.Insert
    End With
End Sub

This is a sheet specific event so it needs to go into the specific sheet's
module.

Hope that helps,

Smitty

Mrfunpantz · #3 (**permalink**) 03-25-04, 13:39

wow...I think I may have asked for more than I can handle. I have no idea what you just put there. I was hoping there was some simple way to do it through the excel menues and functions. I think I will need a website that will teach me the Basics of VB.

Smitty · #4 (**permalink**) 03-25-04, 13:55

No problem. That's why everyone is here.

In your workbook, hit ALT+F11. That will launch the Visual Basic Editor.

In the left hand pane (that's the Project Explorer), you'll see VBAProject(YourFileName.xls). When you expand it you'll see a folder named Microsoft Excel Objects. Open it and double click on the sheet where you want the rows entered. If none of that is visible, then hit CTRL+R to open the Explorer.

A blank window will open on the right-hand side. Paste the code that I posted in there and hit ALT+Q to return to Excel.

Now change/enter something in column D and see what happens.

The best way to learn this stuff is to start playing with the macro recorder. When you record, VBA will create a folder called Modules. In each instance of Excel, when you record macros, VBA will put them in successive modules. Module1, Module2, etc.

Just note that the recorder records literally every move that you make so you'll wind up with a lot of unnecessary code that will need to be cleaned for performance.

Post back with more questions!

Smitty

EDIT: here's a website that will give you an introduction to Excel VBA. It's a pretty good tutorial. http://maths.sci.shu.ac.uk/units/ioa/

Mrfunpantz · #5 (**permalink**) 04-01-04, 18:52

Hey thanks for the info. I was waiting for another email telling me there was a response to the forum, I guess I better just check the forum regularly. I will follow your instructions and let you know how it all turns out. Thanks!!

Smitty · #6 (**permalink**) 04-01-04, 22:00

Anytime!

I've missed out on a post response or two as well. Don't know if it's the site our the fact that we use Lotus Not(S).

Smitty

Internet Services

Web Development

Digital Marketing

Consumer Tech