deleting with a for loop

F

Flyingmeatball

Board Regular
Joined
Aug 15, 2007
Messages
65
I have a for loop that goes through cells in Column A, and deletes them if they are 0 or blank. However, if i delete a cell, the next row is in effect skipped, because the cells shift up. How can I make the for loop run twice if it deleted a cell? I tried deleteloop = deleteloop -1, but that just hangs my excel and i have to break the code. Here is the code:

Sub clearblanks()

Dim lastopen As Integer
With Sheets("upload file")
lastopen = .Cells(.Rows.Count, "A").End(xlUp).Row - 19
End With

For deleteloop = 20 To 20 + lastopen

If Cells(deleteloop, 4).Text = "" Or Cells(deleteloop, 4).Value = 0 Then
Rows(deleteloop).Delete
End If

Next deleteloop

End Sub
 
Sort by date Sort by votes
There's a small nuance if there are blank cells in the range though - you'll have to delete them first before you apply and use the autofilter. :)

Something like....

Code: 
Sub Example1()
    With Sheet1
        .AutoFilterMode = False
 
        'get rid of those pesky blank cells
        On Error Resume Next
        .Range(.Cells(20, 1), .Cells(.Rows.Count, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        On Error GoTo 0
        
        'now let's find those 0 cells
        .Cells(19, 1).AutoFilter Field:=1, Criteria1:=0
 
        ' the first row had headers
        On Error Resume Next
        With .AutoFilter.Range
            .Offset(1, 0).Resize(.Rows.Count - 1, 1). _
                SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With
        On Error GoTo 0
 
        .AutoFilterMode = False
 
    End With
 
End Sub
 
Last edited:
Upvote 0

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Right, in my example I just specified the header range (a single cell) so the blank cells were prematurely ending the autofilter range, but if you tell it the whole column (or whole "used range") under the header then that would work (and it's better). Sorry I should have been more specific before I jumped in!
Code: 
Sub Example1()
    With Sheet1
        .AutoFilterMode = False
 
        .Range(.Cells(19, 1), .Cells(.Rows.Count, 1)).AutoFilter _
                                Field:=1, _
                                Criteria1:="=", _
                                Operator:=xlOr, _
                                Criteria2:=0
 
        ' the first row had headers
        On Error Resume Next
        With .AutoFilter.Range
            .Offset(1, 0).Resize(.Rows.Count - 1, 1). _
                SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With
        On Error GoTo 0
 
        .AutoFilterMode = False
 
    End With
 
End Sub
 
Upvote 0
Colin_L said:
Right, in my example I just specified the header range (a single cell) so the blank cells were prematurely ending the autofilter range, but if you tell it the whole column (or whole "used range") under the header then that would work (and it's better).
Click to expand...
Yes, if there were to be an entire row with no data resulting in a non-contiguous range being the target, that would indeed need to be compensated for. However, since the OP's original code showed him using an .end(xlup) method, I figured he would continue to use this construct to size the target range for the Autofilter method, rather than relying on [A1]'s .currentregion to set the target range. And there's always the chance that meatball tries using the implied .currentregion and it fails. Then he'd learn an important lesson about the plusses and minuses of the various options for setting the target range. :wink:
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Question
how to delete columns in a pattern.
Replies
5
Views
208
Zeema
Z
I
  • Solved
Select next row at column A after row has been deleted
Replies
4
Views
115
ipbr21054
I
Romano_odK
  • Question
Making it filter and not delete the lines
Replies
3
Views
178
Peter_SSs
Peter_SSs
B
  • Question
How to Create a Loop to ignore BLANK CELLS
Replies
3
Views
254
Alex Blakenburg
A
P
  • Question
Rationalise Code
Replies
1
Views
178
mumps
mumps

Forum statistics

Threads
1,215,456
Messages
6,124,939
Members
449,197
Latest member
k_bs

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top