Loop

[desmondleow]

I need assistance with the following code.

I am trying to run through a table of values. For this particula
table, there are empty rows which i would like to delete. There can b
one or more empty rows in the table.

I have written the code but it doesn't work properly because the loo
never ends. Can somebody help me on this? Thanks a lot!

With Worksheets("SGTemp")
intRow = 2
intLastRow = .Cells(Rows.Count, 1).End(xlUp).Row

For intRow = 2 To intLastRow
If IsEmpty(.Cells(intRow, 1)) Then
Range("A" + Format(intRow) + ":B"
Format(intRow)).Select
Selection.Delete Shift:=xlUp
intRow = intRow - 1
End If
Next intRow
End Wit

 

[Chip Pearson]

Desmond,

When you delete rows in a loop, you should start at the last row
and work your way upwards, rather than starting at the top and
going down. Moreover, there is no reason to Select the cells
prior to deleting them.

With Worksheets("SGTemp")
intRow = 2
intLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
For intRow = intLastRow To 2 Step -1
If IsEmpty(.Cells(intRow, 1)) Then
Cells(intRow,1).Resize(1,2).Delete Shift:=xlUp
End If
Next intRow
End With

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com