Macro to insert blank lines
 

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.

--
Terry, West Sussex, UK

Macro to insert blank lines
 

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


--
Don Guillett
SalesAid Software

"Terry Pinnell" wrote in message
...
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.

--
Terry, West Sussex, UK

Macro to insert blank lines
 

"Don Guillett" wrote:

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'.

--
Terry, West Sussex, UK

Macro to insert blank lines
 

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 ??

--
Don Guillett
SalesAid Software

"Terry Pinnell" wrote in message
...
"Don Guillett" wrote:

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'.

--
Terry, West Sussex, UK

Macro to insert blank lines
 

"Don Guillett" wrote:

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, West Sussex, UK

Macro to insert blank lines
 

Terry Pinnell wrote:

"Don Guillett" wrote:

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

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

--
Terry, West Sussex, UK

Macro to insert blank lines
 

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.

--
Don Guillett
SalesAid Software

"Terry Pinnell" wrote in message
...
Terry Pinnell wrote:

"Don Guillett" wrote:

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

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

--
Terry, West Sussex, UK