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\or tv0410.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_ch eck.sh-arc
N:\admin\oracle_dba\archives\bin\usr\prodaccess_ch eck.sh-arc
N:\admin\oracle_dba\archives\PVCS\config\ncs_maste r.cfg-arc
N:\admin\oracle_dba\archives\PVCS\config\ncs_maste r.cfg-arc

I would want to insert a blank row after
\mnt\snd\pvcs\ncs\abctv\archives\oracle_reports\or tv0410.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

insert a blank row after data changes in a column
 

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" wrote in message
...
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\or tv0410.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_ch eck.sh-arc
N:\admin\oracle_dba\archives\bin\usr\prodaccess_ch eck.sh-arc
N:\admin\oracle_dba\archives\PVCS\config\ncs_maste r.cfg-arc
N:\admin\oracle_dba\archives\PVCS\config\ncs_maste r.cfg-arc

I would want to insert a blank row after
\mnt\snd\pvcs\ncs\abctv\archives\oracle_reports\or tv0410.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

insert a blank row after data changes in a column
 

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" wrote in message
...
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\or tv0410.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_ch eck.sh-arc
N:\admin\oracle_dba\archives\bin\usr\prodaccess_ch eck.sh-arc
N:\admin\oracle_dba\archives\PVCS\config\ncs_maste r.cfg-arc
N:\admin\oracle_dba\archives\PVCS\config\ncs_maste r.cfg-arc

I would want to insert a blank row after
\mnt\snd\pvcs\ncs\abctv\archives\oracle_reports\or tv0410.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