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

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
[email protected]
"Terry Pinnell" <[email protected]> wrote in message
news:[email protected]...
> 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

"Don Guillett" <[email protected]> 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

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
[email protected]
"Terry Pinnell" <[email protected]> wrote in message
news:[email protected]...
> "Don Guillett" <[email protected]> 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

"Don Guillett" <[email protected]> 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

Terry Pinnell <[email protected]> wrote:

>"Don Guillett" <[email protected]> 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