CODEWITHSUNDEEP
excelvba
Han JIAO
Han JIAO

Reputation: 3

SpecialCells(xlCellTypeVisible) choose the whole worksheet

I am Sorry to make the question unclear. Here is an example from Error when I use SpecialCells of AutoFilter to get visible cells in VBA changed the code:

Sub Sample():
    ActiveSheet.AutoFilterMode = False

    Dim rRange As Range
    Dim Rnge As Range
    Dim last_Row As Integer

    Set rRange = Sheets("Sheet1").Range("A1:F6")

    '~~> Filter,
    With rRange
      .AutoFilter Field:=1, Criteria1:="=1"
    End With

    last_Row = Workbooks("Book1").Sheets("Sheet1").Range("A1048576").End(xlUp).Row

      '~~> Offset(to exclude headers)
    Set Rnge = Range("A2:A" & last_Row).SpecialCells(xlCellTypeVisible)

    Debug.Print Rnge.Address
    Debug.Print last_Row

End Sub

Samples data:

Number1 Number2 Number3 Number4 Number5 Number6
1       1       1       1       1       1
2       2       2       2       2       2
3       3       3       3       3       3
4       4       4       4       4       4
5       5       5       5       5       5

If I set the criteria = 2. Then the debug.print will give me the row3. But if I set the criteria = 1. The it returns $1:$2,$7:$1048576 2. What happend?

Upvotes: 0

Views: 3111

Answers (1)

user4039065
user4039065

Reputation:

This is a known problem with attempting .SpecialCells(xlCellTypeVisible) on a single first row (your range is Range("A2:A2") when last_row is 2). If you set .SpecialCells(xlCellTypeVisible from a single row of filtered data it includes the data range outside of the filtered range, If you set last_row before filtering, you would get the correct results assuming you had more than a single row of data to start with.

    last_Row = Sheets("Sheet1").Range("A" & rows.count).End(xlUp).Row
    Set rRange = Sheets("Sheet1").Range("A1:F" & last_Row)

    '~~> Filter,
    With rRange
      .AutoFilter Field:=1, Criteria1:="=1"
    End With

I prefer to work strictly within the range to be filtered and use the worksheet function .Subtotal to determine if there are any cells to process.

Sub Sample2()
    ActiveSheet.AutoFilterMode = False

    With Sheets("Sheet1").Cells(1, 1).CurrentRegion
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:="=1"

        With .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count)
            If Application.Subtotal(103, .Columns(1)) Then
                Debug.Print .Columns(1).SpecialCells(xlCellTypeVisible).Address(0, 0)
                Debug.Print .SpecialCells(xlCellTypeVisible).Rows.Count
            Else
                Debug.Print "no cells were found"
            End If
        End With

    End With
End Sub

The .CurrentRegion returns the block or island of date encompassing A1 until it meets a fully blank row or column. This is the same as selecting A1 and tapping Ctrl+A. By working strictly within the confines of CurrentRegion (remember to resize when offsetting), you can avoid a range reference that might spill into the rest of the worksheet where the visible rows will be the remainder of the worksheet's cells.

There was a KB whitepaper on this but regretfully I could not locate it.

Upvotes: 3

Related Questions