Get All Visible Rows After AutoFilter

wsnyder

Board Regular
Joined
Sep 23, 2018
Messages
137
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

VBA Code:
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,936
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
    With rng
        Debug.Print .Columns(1).SpecialCells(xlVisible).Count - 1
        Set xrng = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
    End With
 

JEC

Well-known Member
Joined
Aug 21, 2021
Messages
566
Office Version
  1. 365
Platform
  1. Windows
After filtering, you want to copy the range to another sheet?
 

wsnyder

Board Regular
Joined
Sep 23, 2018
Messages
137
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

Board Regular
Joined
Sep 23, 2018
Messages
137
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

VBA Code:
 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
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,936
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
1,148,140
Messages
5,745,035
Members
423,917
Latest member
Frank1931

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top