Macro to insert blank lines

[Terry Pinnell]

I wonder if anyone already has a macro for the following operation I
perform quite often please? I insert blank lines in a long table
whenever a particular column changes. A typical example is in a list
containing a Year column, sorted by Year. It improves readability to
add a blank line between the 1972 and 1973 sections, 1973 and
1974...etc.

 

[Don Guillett]

try
Sub InsertRow()
For i = cells(rows.count,"a").End(xlUp).Row To 2 Step -1
If Cells(i, 1).Value <> Cells(i - 1, 1).Value Then
Rows(i).Insert
End If
Next
End Sub

 

[Terry Pinnell]

try
Sub InsertRow()
For i = cells(rows.count,"a").End(xlUp).Row To 2 Step -1
If Cells(i, 1).Value <> Cells(i - 1, 1).Value Then
Rows(i).Insert
End If
Next
End Sub

Thanks. Now realise how rusty I am - it's been so long since I
imported a macro (much less wrote one) that I've forgotten the very
basics!

With my target worksheet open I pasted that (renamed InserBlankRow,as
I wrote one called InsertRow many years ago) into VB Editor. What does
the macro expect by way of starting conditions? Anyway, whatever cell
or cells I selected beforehand, I got 'Compile error: Variable not
defined'.

 

[Don Guillett]

Pretty straight forward.
It is not using a "selection". Assumes your data to look at is in col A. It
works from the bottom up to compare the cell above and insert a row if they
DON'T match. If not col a, change the "a" to whatever column and the
cells(i,1) to ,2 or ,3 or ??

 

[Terry Pinnell]

Pretty straight forward.
It is not using a "selection". Assumes your data to look at is in col A. It
works from the bottom up to compare the cell above and insert a row if they
DON'T match. If not col a, change the "a" to whatever column and the
cells(i,1) to ,2 or ,3 or ??

So it doesn't matter what is highlighted when I run it?

I'm still getting that same error message, so I assume I did something
wrong when that by now unfamiliar VBA Editor window was displayed. I
pasted it into a 'Module', as you see here.
http://www.terrypin.dial.pipex.com/Images/Macro1.gif

 

[Terry Pinnell]

So it doesn't matter what is highlighted when I run it?

I'm still getting that same error message, so I assume I did something
wrong when that by now unfamiliar VBA Editor window was displayed. I
pasted it into a 'Module', as you see here.
http://www.terrypin.dial.pipex.com/Images/Macro1.gif

Success! It must have been that Option Explicit line, because after
deletion it your macro worked fine, thanks.

 

[Don Guillett]

If you use option explicit you must declare all variables which is a very
good idea but not always necessary. The really good programmers always do
it. Guess what that makes me? Anyway, thanks for the kind words.