Macro To Insert Row

[Jeremy]

I need to create a macro to insert 4 rows inbetween where the data in column
G changes from ZB to ZA? I am not good at entering macros in VB so A little
hand holding would be great. Thanks

 

[Gord Dibben]

First post said ZA to ZB and one row, now ZB to ZA and 4 rows.

Which is it?

Any values in column G other than ZA or ZB?


Gord Dibben MS Excel MVP

 

[Jeremy]

The column Has a Header In G1, then (after looking at the sheet again) it is
Z9 and ZB. I just need to be able to have a macro that will insert a row (or
4 rows, doesn't matter) when the data in column g changes from Z9 to ZB.

 

[Gord Dibben]

I assume from your reply that Z9 and ZB are the only values other than the
header value.

Try this macro which will insert 4 rows at any change in value in column G.

Ignores header value.

Sub InsertRow_At_Change()
'Sandy Mann July 1st, 2007
Dim LastRow As Long
Dim X As Long
LastRow = Cells(Rows.Count, 7).End(xlUp).Row
Application.ScreenUpdating = False

For X = LastRow To 3 Step -1
If Cells(X, 7).Value <> Cells(X - 1, 7).Value Then
If Cells(X, 7).Value <> "" Then
If Cells(X - 1, 7).Value <> "" Then
Cells(X, 7).Resize(4, 1).EntireRow.Insert ''' Shift:=xlDown
End If
End If
End If
Next X
Application.ScreenUpdating = True
End Sub


Gord

 

[Jeremy]

That worked beautifully. One last question. How do I save the macro to my
personal worksheet with a ctrl+ (KEY) functin attached to it?

 

[Dave Peterson]

Open your personal.xls file
Alt-f8
select the macro you want
Click on Options
Assign the shortcut key that you want
Close the dialog
Close excel (and save personal.xls, too)

Reopen excel and test it with the shortcut key.

 

[Studebaker]

Hello Gord Dibben,

This macro is what I needed to insert rows on my spreadsheet--I used mine to
just insert 2 rows.

Using this macro, what code would I add to it to: a) highlight the newly
inserted rows gray (not entire row just row to end of my table) and b) add
the thin single line border just around the edges of the new rows (not the
interior)?

I tried using another's code but it didn't work and I haven't heard back
from them.

c) I also need to be able to reverse all of the above for spreadsheets I've
had to do manually--without code. Ex: I need to remove the 2 inserted rows,
the gray highlighting and the borders so that the spreadsheet looks back to
normal?

I hope it's not a tall order. I would really appreciate the help.

Thank you!