Loop through records
 

Hello everyone,
i am new to vba and excel. In column A i have 47000 rows, i want to
loop through this column and if cell1 does not equal cell2 i want to
add a row between cell1 and cell2. I cannot figure out how to do this..
can anyone help me please??/
Thanks,
Erin

Loop through records
 

Erin,
Try this (on some test data first!)

Sub InsertRows()

Dim lastrow as long, r as long

lastrow = Cells(Rows.Count, "A").End(xlUp).Row

For r = lastrow To 2 Step -1

If Cells(r, 1) < Cells(r - 1, 1) Then
Cells(r, 1).Insert shift:=xlDown
End If

Next r

End Sub

HTH

" wrote:

Hello everyone,
i am new to vba and excel. In column A i have 47000 rows, i want to
loop through this column and if cell1 does not equal cell2 i want to
add a row between cell1 and cell2. I cannot figure out how to do this..
can anyone help me please??/
Thanks,
Erin

Loop through records
 

Ok but how do i insert a full row, i have 8 columns and i want to
insert the row across all of the columns.
Thanks so much!!!

Loop through records
 

if you only want to insert a blank cell in column A (or whatever column you
adjust it to operate on), then you have a solution. I f you actually want
to insert a blank row, change

Cells(r,1).Insert Shift:=xldown
to

rows(r).Insert



--
Regards,
Tom Ogilvy


"Toppers" wrote in message
...
Erin,
Try this (on some test data first!)

Sub InsertRows()

Dim lastrow as long, r as long

lastrow = Cells(Rows.Count, "A").End(xlUp).Row

For r = lastrow To 2 Step -1

If Cells(r, 1) < Cells(r - 1, 1) Then
Cells(r, 1).Insert shift:=xlDown
End If

Next r

End Sub

HTH

" wrote:

Hello everyone,
i am new to vba and excel. In column A i have 47000 rows, i want to
loop through this column and if cell1 does not equal cell2 i want to
add a row between cell1 and cell2. I cannot figure out how to do this..
can anyone help me please??/
Thanks,
Erin

Loop through records
 

THANKS!!!!!!!!!!!

Loop through records
 

Sub InsertRows()
Do Until ActiveCell.Value = ""
If ActiveCell.Value < ActiveCell.Offset(1, 0).Value Then
ActiveCell.Offset(1, 0).Activate
Current_Row = ActiveCell.Row
Rows(Current_Row).Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(1, 0).Activate
Else
ActiveCell.Offset(1, 0).Activate
End If
Loop
End Sub

Try that out

Loop through records
 

Thanks Tom. The OP did say insert row so the error was mine!

"Tom Ogilvy" wrote:

if you only want to insert a blank cell in column A (or whatever column you
adjust it to operate on), then you have a solution. I f you actually want
to insert a blank row, change

Cells(r,1).Insert Shift:=xldown
to

rows(r).Insert



--
Regards,
Tom Ogilvy


"Toppers" wrote in message
...
Erin,
Try this (on some test data first!)

Sub InsertRows()

Dim lastrow as long, r as long

lastrow = Cells(Rows.Count, "A").End(xlUp).Row

For r = lastrow To 2 Step -1

If Cells(r, 1) < Cells(r - 1, 1) Then
Cells(r, 1).Insert shift:=xlDown
End If

Next r

End Sub

HTH

" wrote:

Hello everyone,
i am new to vba and excel. In column A i have 47000 rows, i want to
loop through this column and if cell1 does not equal cell2 i want to
add a row between cell1 and cell2. I cannot figure out how to do this..
can anyone help me please??/
Thanks,
Erin