CODEWITHSUNDEEP
vbaexcel
Tawm
Tawm

Reputation: 545

VBA: AutoFilter returns TRUE; For Each loop hits every other cell

I'm trying to run the below Sub within another Sub.

The issue I'm having is wRange.AutoFilter(3, "") fills all cells in wRange with TRUE, overwriting the data which was previously in them.

Just to see if it would work aside from the TRUE issue, I changed the line If wCell = "" Then to If wCell = "True" and I see that it does not delete every row, but rather every other row.

Sub DeleteBlankRows()

Dim wRange As Range
Dim wRange2 As Range
Dim lastrow As Integer

lastrow = Range("A7").End(xlDown).Row
Set wRange = Range("$C$7:$C$" & lastrow)
wRange = wRange.AutoFilter(3, "")

Range("$A$10000").Select
Selection.End(xlUp).Select
If ActiveCell.Row <> 5 Then
    Set wRange2 = wRange.SpecialCells(xlCellTypeVisible)
    For Each wCell In wRange2
        If wCell = "" Then
            wCell.EntireRow.DELETE
        End If
    Next wCell
End If
wRange = wRange.AutoFilter(3)

End Sub

Can you see why it would be doing that?

Thanks for your help.

P.S. As a side note, I originally had the sub has Sub DeleteBlankRows(ByRef wRange) and it didn't have a problem that I hadn't said what wRange2 was. Both of these Dims are stated in the larger Sub which this is run in, but that doesn't make sense to me.

Upvotes: 0

Views: 97

Answers (3)

Rory
Rory

Reputation: 34045

If the cells in question in column C are actually empty, rather than formulas returning "" for example, you could use something like this:

Sub DeleteBlankRows()

    Dim wRange                As Range
    Dim lastrow               As Long

    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    If lastrow > 7 Then
        On Error Resume Next
        Set wRange = Range("C7:C" & lastrow).SpecialCells(xlCellTypeBlanks)
        On Error GoTo 0
        If Not wRange Is Nothing Then wRange.EntireRow.Delete
    ElseIf lastrow = 7 Then
        If Len(Range("C7").Value) = 0 Then Rows(7).Delete
    End If
End Sub

Upvotes: 2

user4039065
user4039065

Reputation:

When deleting rows individually, always work from the bottom to the top. The nature of a For Each wCell In wRange2 is such that you are working from the top to the bottom.

In any event, deleting the rows in bulk would be preferred.

Sub DeleteBlankRows()
    Dim wRange As Range
    Dim lastrow As Long

    With ActiveSheet   '<-set this worksheet erference properly!
        If .AutoFilterMode Then .AutoFilterMode = False
        lastrow = .Range("A7").End(xlDown).Row
        Set wRange = .Range("$A$7:$C$" & lastrow)

        With wRange
            .AutoFilter field:=3, Criteria1:=""
            With .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count)
                If CBool(Application.Subtotal(103, .Columns(1))) Then
                    .SpecialCells(xlCellTypeVisible).EntireRow.Delete
                End If
            End With
            .AutoFilter field:=3
        End With
    End With

End Sub

As per your sample code, I've left the autofilter active but cleared the filter.

Upvotes: 1

Holmes IV
Holmes IV

Reputation: 1739

A better way to check if there is any information in a cell is the IsEmpty function: 

If IsEmpty(cell(1, 1)) = True
       wCell.EntireRow.DELETE
endif

Upvotes: 0

Related Questions