Loop
 

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

--
Message posted from http://www.ExcelForum.com

Loop
 

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


"desmondleow" wrote in
message ...
I need assistance with the following code.

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

I have written the code but it doesn't work properly because
the loop
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 With


---
Message posted from http://www.ExcelForum.com/