Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find loop doesn't loop | Excel Discussion (Misc queries) | |||
help with a loop | Excel Discussion (Misc queries) | |||
getting out of a if loop | Excel Discussion (Misc queries) | |||
HELP!!!! Can't stop a loop (NOT an infinite loop) | Excel Programming | |||
Help with a Loop please | Excel Programming |