CODEWITHSUNDEEP
vbaexceldelete-row
Burton Guster
Burton Guster

Reputation: 2223

VBA: Deleting Rows with a Specific Value

I'm trying to write a macro to delete all rows that have True in column A.

Here is what I have so far:

Sub deleteBlankRows3()
    Dim lastrow as Long
    Dim x as Long

    lastrow = 4650
    For x=8 to x=lastrow
        If (Range("A1").Offset(x,0) = True) Then
            Range("A1").Offset(x,0).EntireRow.Delete
            x = x + 1
        End if
    Next x

End Sub

How can I make the above code work?

Upvotes: 4

Views: 19165

Answers (5)

sirplus
sirplus

Reputation: 535

I had hidden lines and didn't want to unhide them which the filtering method does. Also didn't want to loop through every line so here's my 10c.....

Sub DelError()
    Dim i As Integer
    Dim rngErrRange As Range
    With ActiveSheet
        Do
            Set rngErrRange = .Columns("A:A").Find(What:="#REF!", _
                After:=.Cells(1), LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
                If Not rngErrRange Is Nothing Then
                    rngErrRange.EntireRow.Delete
                Else
                    End
                End If
        Loop
    End With
End Sub

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149335

I know you have already got what you were looking for. However, still here is another method using Autofilter. This is much faster than looping through each row and checking for the value.

Sub Sample()
    Dim lastRow As Long

    With Sheets("Sheet1")

        lastRow = .Range("A" & Rows.Count).End(xlUp).Row

        '~~> Remove any filters
        .AutoFilterMode = False

        '~~> Filter, offset(to exclude headers) and delete visible rows
        With .Range("A1:A" & lastRow)
            .AutoFilter Field:=1, Criteria1:="TRUE"
            .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With

        '~~> Remove any filters
        .AutoFilterMode = False
    End With
End Sub

HTH

Upvotes: 9

ervinbosenbacher
ervinbosenbacher

Reputation: 1780

The problem is that the algorithm is incorrect. Classic case for the corrupted loop variable. The problem is that the variable that the loop is dependent on gets modified, as such it is wrong.

The correct way to do it is this way.

Dim x as integer
x = 8
do
   if (Range("a1").Offset(x, 0) = True) Then
       Range("a1").Offset(x, 0).EntireRow.Delete
   Else
       x = x + 1 'We only increase the row number in the loop when we encounter a row that is false for containing true in cell a1 and their offsets
   End If
Loop Until (x > 4650)

Upvotes: 1

Alain
Alain

Reputation: 27250

Three things might be at work here.

First, you should be looking at the cell's value explicitly if you're testing for equivalence for the underlying value:

If (Range("A1").Offset(x,0).Value = True) Then

Without saying.Value, I think the cell by default returns it's Text property for equivalence tests against a non-range property.

Second, your cells probably contain a string "True", rather than the value True so try using 

If (Range("A1").Offset(x,0).Value = "True") Then

Finally, if you actually find a row, and you delete it, then your will actually end up skipping a row, because all of the rows after the row being deleted will shift down (row 5 becomes row 4, etc), but you also just incremented x, so you will be skipping the row right after every row you deleted. To fix this, either loop in decreasing order:

For x=lastrow to 8 step -1

or don't increment x if you've just deleted a row:

If (Range("A1").Offset(x,0).Value = "True") Then
    Range("A1").Offset(x,0).EntireRow.Delete
Else
    x = x + 1
EndIf

Upvotes: 4

Eddy
Eddy

Reputation: 5370

Without testing you are better off like this:

    For x=lastrow to 8 step -1
        If (Range("A1").Offset(x,0) = True) Then 
           Range("A1").Offset(x,0).EntireRow.Delete 
        End if 
   Next

Counting up has an issue that if you delete one row all rows after it move up as well causing your loop not to look at all rows. And since you add 1 to the x in those cases you made it even worse. -1 would have been better except that then you still check 4650+number_of_deleted_rows in total which might lead to other problems. By starting at the end and move towards the start you prevent both those issues.

Upvotes: 2

Related Questions