Get All Visible Rows After AutoFilter

[wsnyder]

Hi all,

Using Excel 365.

How do I get all visible rows count after autofilter so I can loop through visible values to pass to another sub.?
I tired to get a new Range of visible rows which appears to give the correct non-contiguous address, but not the correct count of visible rows

Thanks,
-w

Option Explicit

Sub foo()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng As Range
    Dim xrng As Range
    Dim rngHeader As Range
    Dim i As Long   'column for filter
    Dim j As Long
    Const word_phrase As String = "Include"
    Const crit As String = "N"
    
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets(1)
    Set rng = ws.Range("A1").CurrentRegion
    Set rngHeader = ws.Rows("1:1")
    
    
    i = FindColumnHeader(rng:=rngHeader, _
                         SearchTerm:=word_phrase)
                        
    Debug.Print "i: "; i
    
    rng.AutoFilter field:=i, _
                   Criteria1:="<>" & crit
                  
    With rng
        Set xrng = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
    End With
                  
    Debug.Print "xrng address: "; xrng.Address
    Debug.Print "xrng rows count: "; xrng.Rows.Count
    Debug.Print "==================================="
    For j = 1 To xrng.Rows.Count
        Debug.Print j, xrng.Cells(j, 1).Value, xrng.Cells(j, 3).Value
    Next j
    
    
    Set rng = Nothing
    Set xrng = Nothing
    Set ws = Nothing
    Set wb = Nothing

End Sub

 

[Fluff]

How about

    With rng
        Debug.Print .Columns(1).SpecialCells(xlVisible).Count - 1
        Set xrng = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
    End With

 

[JEC]

After filtering, you want to copy the range to another sheet?

 

[wsnyder]

Thanks JEC,

I want to pass 1 value at a time to a Pivot Table Filter
Copy the resulting pt to a new sheet in a workbook
Repeat.

The original range has 11 rows in testing (10+header)
I filtered out "N" values
SO I now have 8 rows of data that I need to pass to the pt filter.

Thanks
-w

 

[wsnyder]

I think Fluff's snippet got me going in the right direction
I went with this xrng
I am now getting the 8 expected values

 With rng
        Set xrng = .Offset(1).Resize(.Rows.Count - 1).Columns(1).SpecialCells(xlVisible)
    End With

Output

cell address: $B$2 cell value: 459941
cell address: $B$4 cell value: 460438
cell address: $B$5 cell value: 432862
cell address: $B$6 cell value: 420559
cell address: $B$7 cell value: 439531
cell address: $B$8 cell value: 412914
cell address: $B$10 cell value: 489451
cell address: $B$11 cell value: 438636

Thanks,
-w

 

[Fluff]

Glad we could help & thanks for the feedback.