VBA Autofilter - Removing Visible Cells (seems code should work, but doesn't)

Question

I'm running into some trouble deleting some rows using VBA.

I have data organised in rows in Columns A - K. In column K there is a counter; when it exceeds 1 the entire row needs to be deleted. I set up the following code; and expected it to filter on column K for anything exceeding 1. If it finds that there are more than 1 rows (row 1 is the header), it would delete anything visible, else it just removes the criteria from the filter.

However, the first Message Box returns a value of 2,900 (correct) then the second message box returns a value of 1, and I have no idea why. Consequently, none of the rows with column K exceeding 1 (there are about 2,000) get removed.

Visible Rows is defined at the start of the macro as Long.

        With MySheet

        'Find new lastrow
        lRowDbMsNew = .Cells.Find(What:="*", _
            After:=Range("A1"), _
            LookAt:=xlPart, _
            LookIn:=xlFormulas, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious, _
            MatchCase:=False).ROW

        .Range("A:K").Calculate

        MsgBox ("The last row in the data is " & lRowDbMsNew)

        .Range("A1:A" & lRowDbMsNew).AutoFilter Field:=11, Criteria1:=">1"

            VisibleRows = .Range("A1:A" & lRowDbMsNew).SpecialCells(xlCellTypeVisible).Rows.Count

            MsgBox ("Number of visible rows: " & VisibleRows)

            If VisibleRows > 1 Then

                .Range("A2:A" & lRowDbMsNew).SpecialCells(xlCellTypeVisible).EntireRow.Delete
                .Range("A1:K" & lRowDbMsNew).AutoFilter Field:=11

                Else

                .Range("A1:K" & lRowDbMsNew).AutoFilter Field:=11

            End If

        End With

3 ideas - 1) turn on automatic claculations to True. And do not turn it to false, it is quite dangerous. 2) Make sure you have no `On Error Resume Next` in the code. 3) Read how to debug - https://stackoverflow.com/questions/50189158/debugging-vba-locating-problems-and-troubleshooting-methods — Vityata, Feb 19 '19 at 11:38
Thanks, I'll try running it with calculations set to auto, the macro uses manual as it would otherwise take an age to run. Hence (.Range("A:K").Calculate). There were some On Error resume Next but I removed them. — Dan, Feb 19 '19 at 12:20
If it takes an age to run with automatic calculations, then you should consider rewriting the whole app. There is something wrong in the architecture. — Vityata, Feb 19 '19 at 12:24
That is definitely true, but unfortunately my VBA skills aren't sufficiently up to scratch to refactor the whole thing to be following best practice throughout. For now, the compromises are managable! — Dan, Feb 19 '19 at 15:20

score 1 · Accepted Answer · answered Feb 19 '19 at 12:35

Change your line where you count the number of visible rows to

VisibleRows = .Range("A1:A" & lRowDbMsNew).SpecialCells(xlCellTypeVisible).Count

(remove the .rows).
The .SpecialCells-function returns a non-contiguous Range (in your case containing all cells from column A that are visible), eg $A$1,$A$4:$A$6....
If you check the Rows-propery of that range (which itself is a Range also), you will get the same address(es), however, using the Count-Property returns the number of cells only of the first so called Area of the range.

Check it with this piece of cde:

Dim r As Range
Set r = ThisWorkbook.Sheets(1).Range("A1:A" & lRowDbMsNew).SpecialCells(xlCellTypeVisible)
Debug.Print r.Address
Debug.Print r.Count
Dim r2
Set r2 = r.Rows
Debug.Print r2.Address
Debug.Print r2.Count

VBA Autofilter - Removing Visible Cells (seems code should work, but doesn't)

1 Answers1