insert a blank row after data changes in a column

Hi,
I have serveral very large spread sheets, and I need to insert a blank row
when the data changes in a column for example

\mnt\snd\pvcs\ncs\abctv\archives\oracle_reports\ortv0410.rdv
N:\admin\oracle_dba\archives\bin\usr\prodaccess.sh-arc
N:\admin\oracle_dba\archives\bin\usr\prodaccess.sh-arc
N:\admin\oracle_dba\archives\bin\usr\prodaccess_check.sh-arc
N:\admin\oracle_dba\archives\bin\usr\prodaccess_check.sh-arc
N:\admin\oracle_dba\archives\PVCS\config\ncs_master.cfg-arc
N:\admin\oracle_dba\archives\PVCS\config\ncs_master.cfg-arc

I would want to insert a blank row after
\mnt\snd\pvcs\ncs\abctv\archives\oracle_reports\ortv0410.rdv and
N:\admin\oracle_dba\archives\bin\usr\prodaccess.sh-arc

I tried uisng the a2=a3 formula, then sort for false and select only
visible cells, but it was a little funky
Is there a macro or something I could use to do this? I am using Excel 2003
Cyndi

suppose your data start from A1(data without headers if any)
sort the columnA (only data portion)
the test starts from A2

then try run this sub

Public Sub test()
Dim myrange As Range
Dim cell As Range
Set myrange = Range(Range("a2"), Range("a2").End(xlDown))
For Each cell In myrange
cell.Activate
If cell.Offset(-1, 0) = "" Then GoTo line1
If cell <> cell.Offset(-1, 0) Then
ActiveCell.Rows.Insert
Set cell = cell.Offset(1, 0)
End If
line1:
Next
MsgBox "macro over"
End Sub


--
remove $$$ from email addresss to send email

"cyndi" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> I have serveral very large spread sheets, and I need to insert a blank row
> when the data changes in a column for example
>
> \mnt\snd\pvcs\ncs\abctv\archives\oracle_reports\ortv0410.rdv
> N:\admin\oracle_dba\archives\bin\usr\prodaccess.sh-arc
> N:\admin\oracle_dba\archives\bin\usr\prodaccess.sh-arc
> N:\admin\oracle_dba\archives\bin\usr\prodaccess_check.sh-arc
> N:\admin\oracle_dba\archives\bin\usr\prodaccess_check.sh-arc
> N:\admin\oracle_dba\archives\PVCS\config\ncs_master.cfg-arc
> N:\admin\oracle_dba\archives\PVCS\config\ncs_master.cfg-arc
>
> I would want to insert a blank row after
> \mnt\snd\pvcs\ncs\abctv\archives\oracle_reports\ortv0410.rdv and
> N:\admin\oracle_dba\archives\bin\usr\prodaccess.sh-arc
>
> I tried uisng the a2=a3 formula, then sort for false and select only
> visible cells, but it was a little funky
> Is there a macro or something I could use to do this? I am using Excel
2003
> Cyndi

your the best, this worked perfect, Thanks so much!!!

"R.VENKATARAMAN" wrote:

> suppose your data start from A1(data without headers if any)
> sort the columnA (only data portion)
> the test starts from A2
>
> then try run this sub
>
> Public Sub test()
> Dim myrange As Range
> Dim cell As Range
> Set myrange = Range(Range("a2"), Range("a2").End(xlDown))
> For Each cell In myrange
> cell.Activate
> If cell.Offset(-1, 0) = "" Then GoTo line1
> If cell <> cell.Offset(-1, 0) Then
> ActiveCell.Rows.Insert
> Set cell = cell.Offset(1, 0)
> End If
> line1:
> Next
> MsgBox "macro over"
> End Sub
>
>
> --
> remove $$$ from email addresss to send email
>
> "cyndi" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> > I have serveral very large spread sheets, and I need to insert a blank row
> > when the data changes in a column for example
> >
> > \mnt\snd\pvcs\ncs\abctv\archives\oracle_reports\ortv0410.rdv
> > N:\admin\oracle_dba\archives\bin\usr\prodaccess.sh-arc
> > N:\admin\oracle_dba\archives\bin\usr\prodaccess.sh-arc
> > N:\admin\oracle_dba\archives\bin\usr\prodaccess_check.sh-arc
> > N:\admin\oracle_dba\archives\bin\usr\prodaccess_check.sh-arc
> > N:\admin\oracle_dba\archives\PVCS\config\ncs_master.cfg-arc
> > N:\admin\oracle_dba\archives\PVCS\config\ncs_master.cfg-arc
> >
> > I would want to insert a blank row after
> > \mnt\snd\pvcs\ncs\abctv\archives\oracle_reports\ortv0410.rdv and
> > N:\admin\oracle_dba\archives\bin\usr\prodaccess.sh-arc
> >
> > I tried uisng the a2=a3 formula, then sort for false and select only
> > visible cells, but it was a little funky
> > Is there a macro or something I could use to do this? I am using Excel
> 2003
> > Cyndi
>
>
>
>