CODEWITHSUNDEEP
vbaexcel
Arturo Esparza
Arturo Esparza

Reputation: 7

ActiveCell.EntireRow.Delete

I have this VBA code to delete rows in excel

Sub deleterows() 
i = 1       
Do Until i = 150000
If ActiveCell.Value = False Then 
ActiveCell.EntireRow.Delete
End If
ActiveCell.Offset(1, 0).Activate
i = i + 1
Loop
End Sub

However this code is not deleting all the rows that contain the "False" value, I've been trying to change it to activecell.value="" and activecell.value=vbnullstring but still it does not deletes all blank rows

Upvotes: 0

Views: 10103

Answers (3)

Aurean
Aurean

Reputation: 24

Here's a handful of nice things baked in to what it looks like what you want to accomplish.

I'm assuming that 150000 is basically just a big number so that you are confident that all used rows are being considered.

Sub DeleteRows()
i = 0
Do While ActiveCell.Offset(i, 0).Row <= ActiveCell.End(xlDown).Row
'This only considers used rows - much better than considering 15,000 rows even if you're only using 100
If ActiveCell.Offset(i, 0).Value <> 1 Then
'If you directly have a boolean value (i.e. 'True', 'False', or '0','1', you do not need to compare to another value. If your cells contain text, compare with the string in quotes (i.e. ...= "False")
ActiveCell.Offset(i, 0).Delete
Else: i = i + 1
End If
'Don't have to activate the next cell because we're referencing off of a fixed cell
Loop
End Sub

Upvotes: 0

BruceWayne
BruceWayne

Reputation: 23285

You should move from the last row to the top, if you're deleting rows.

Also, it's best to avoid using ActiveCell.

Sub deleterows2()
i = 1
For i = 150000 To 1 Step -1
    If Cells(i, 1).Value = False Or Cells(i, 1).Value = "False" Then
        Rows(i).EntireRow.Delete
    End If
Next i
End Sub

Tweak as needed. I'm assuming your column A has the cells you're checking for. If it's another column, just use that column's index number in the Cells(i,1). So if you need to check column D, use Cells(i,4)

Upvotes: 2

isaace
isaace

Reputation: 3429

You can fix it with a small change as follows:

If ActiveCell.Value = False Then
    ActiveCell.EntireRow.Delete
Else
    ActiveCell.Offset(1, 0).Activate
End If

Basically, you should only activate the next sell when the value is != False, otherwise it will skip rows.

Upvotes: 0

Related Questions