VBA - Count filtered rows in the table

**DocAElstein** · 08-23-2023, 03:15 PM

I have a quick question on you….
Are you results here: https://www.excelfox.com/forum/showt...3218#post23218 , supposed to be the correct results for the text or csv file?

My results ( with any of my or your text or csv files are different. )

For example, filter first FName of rr
I only ever find 6 to filter

Your result

Code:

 "Total rows: 100
Name filter: 80 Left / Filtered 20
Region filter: 72 Left / Filtered 8
Country filter: 22 Left / Filtered 50"

My results, (with any of the text files or csv files)

Code:

 "Total rows: 100
name filter by rr: 94 Left / Filtered 6
region filter by sh: 90 Left / Filtered 4
country filter by us: 86 Left / Filtered 4
"

How do you filter 20 by rr from this

Code:

name
Pascale Murray
Harrison Irwin
Kim Buckner
Mia Cruz
Sierra Howe
Kyra Osborne
Matthew Jenkins
Adria Mayo
Patricia Rodriguez
Nolan Hurst
Cleo Munoz
Scarlet David
Madonna Parks
Derek Cain
Zorita Harvey
Lev Stout
George Blake
Adele Burgess
Curran Herring
Jenette Stanton
Katell Whitaker
Phillip Hunter
Martena Cotton
Brenda Mclaughlin
Brett Blackwell
Alma Curtis
Ainsley Battle
Glenna Gregory
Julian Castillo
Beatrice Whitley
Brooke Daniel
Nicholas Douglas
Kelly Ware
Peter Velazquez
Erasmus Hughes
Reese Bray
MacKenzie Ray
Audra Nolan
Jin Mcmillan
Alec Guzman
Ciara Hurst
Yasir Rollins
Logan Lara
Wayne Holcomb
Madonna Ayers
Christian Graves
Ingrid Harper
Rafael Emerson
Chandler Burks
Jaquelyn Hendrix
Roanna Reynolds
Chandler Baldwin
Wanda Luna
Blake Herring
Odette Gordon
Ignacia Randolph
Haley Ewing
Keaton Mckay
Yeo Allen
Chester Whitfield
Kadeem Calhoun
Clinton Mcgowan
Macon Burke
Yen Griffin
Dieter Christensen
Rafael Ferguson
Rana Graves
Hilel Marshall
Lev Osborne
Amela Benjamin
Brock Gomez
Raphael Gonzalez
Armando Roach
Iona Glenn
Rose Brennan
Adara Mcguire
Jarrod Mccarthy
Aiko Hardy
Blythe Knapp
Jameson Ramsey
Idona Flynn
Rogan Mcneil
Kiayada Spencer
Dillon Dunlap
Anastasia Powers
Neil Walter
Jin Gilliam
Stephanie Mann
Petra Sellers
Armando Fuentes
Amy Eaton
Kessie Parsons
Bo Irwin
Ariel Rice
Chancellor Ratliff
Jana Hickman
Halla Hodges
Tiger Gay
Kenneth Delacruz
Mikayla Pacheco

Is the answer…
First you filter FName ll
then FName by rr

Then FRegion by sh

Then FCountry by us

If that is the answer, then I will have to re write my macro because it does not do that. My macro just uses the last things added, so it does this

Is the answer…
First FName by rr

Then FRegion by sh

Then FCountry by us

**sandy666** · 08-23-2023, 06:35 PM

here is some results

Count

Total rows: 100
Name filter: 94 Left / Filtered 6
Region filter: 94 Left / Filtered 0
Country filter: 94 Left / Filtered 0

Fname= rr

Count

Total rows: 100
Name filter: 80 Left / Filtered 20
Region filter: 80 Left / Filtered 0
Country filter: 80 Left / Filtered 0

Fname= rr, ll

Count

Total rows: 100
Name filter: 80 Left / Filtered 20
Region filter: 72 Left / Filtered 8
Country filter: 72 Left / Filtered 0

Fname= rr, ll

Fregion= sh

Count

Total rows: 100
Name filter: 80 Left / Filtered 20
Region filter: 72 Left / Filtered 8
Country filter: 64 Left / Filtered 8

Fname= rr, ll

Fregion= sh

Fcountry= us

Count

Total rows: 100
Name filter: 100 Left / Filtered 0
Region filter: 96 Left / Filtered 4
Country filter: 92 Left / Filtered 4

Fname=

Fregion= sh

Fcountry= us

Count

Total rows: 100
Name filter: 100 Left / Filtered 0
Region filter: 96 Left / Filtered 4
Country filter: 96 Left / Filtered 0

Fname=

Fregion= sh

Fcountry=

Count

Total rows: 100
Name filter: 100 Left / Filtered 0
Region filter: 100 Left / Filtered 0
Country filter: 96 Left / Filtered 4

Fname=

Fregion=

Fcountry= us

Count

Total rows: 100
Name filter: 100 Left / Filtered 0
Region filter: 100 Left / Filtered 0
Country filter: 100 Left / Filtered 0

Fname=

Fregion=

Fcountry=

**DocAElstein** · 08-23-2023, 06:46 PM

OK, so answer is like

Originally Posted by DocAElstein

.....the answer…
First you filter FName by ll
then FName by rr

Then FRegion by sh

Then FCountry by us
...

**sandy666** · 08-23-2023, 07:01 PM

filters filtering columns but rows are counting for whole table, that is why this is an Excel Table (source from CSV) not Range

**DocAElstein** · 08-23-2023, 07:24 PM

Originally Posted by sandy666

filters filtering columns but rows are counting for whole table, ….

Yep, I guessed that bit right already, …but I almost didn’t, - so in other words you remove(filter) entire rows in the table where you find, for example, rr , in the first table column.

I think I am either 100% there in understanding what you want now, or I am very close.

I also know exactly how you want the final output. I will try to do that eventually. But if I had originally tried to do the output exactly as you wanted from the start, then
_ it would be more difficult for me to change it now,
and
_ I might not have noticed my mistake….

….I have not done so much Excel Filter work so I did not know how filters on table typically work….. I know you know but I forgot my hairy fucking Crystal Bollocks and didn't know

Originally Posted by sandy666

…., that is why this is an Excel Table (source from CSV) not Range

I don’t know anything about that, I don’t know what is a Poxy fucking Excel Table is either

Never mind, it's probably not important

I will take anothert look later.- I am going now for my long daily jog along my the old railway track.

Its beautiful. I do it early while it’s still sunny, so I get a nice Sun tan on my body so that I will be even more beautiful in my YouTube videos...

**sandy666** · 08-23-2023, 07:46 PM

Take more pics from your jogging track
Your YT videos = 0

**DocAElstein** · 08-24-2023, 01:48 PM

Hallo

I am getting some different results to yours… I don’t understand yet why

Example:

Your results:

Count

Total rows: 100
Name filter: 80 Left / Filtered 20
Region filter: 72 Left / Filtered 8
Country filter: 64 Left / Filtered 8

Fname= rr, ll

Fregion= sh

Fcountry= us

My results:

Code:

 "name filter rr ll : 80 Left / Filtered 20
Region filter sh : 78 Left / Filtered 2
Country filter us : 75 Left / Filtered 3
alan filter big : 73 Left / Filtered 2
"

?? – Problem is Region filter sh and Country filter us
See uploaded file, see worksheet items 3 and 4 ( "TemporaryWorksheet" and "Tables" )

Country filter us
See worksheet items 4 ( "Tables" )
In country column there are 4 with us
Russian Federation
Australia
Russian Federation
Austria

Now see worksheet items 3 ( "TemporaryWorksheet" )
These are taken out by Country filter us Filtered 3
Russian Federation
Australia
Russian Federation
This is taken out previously by name filter ll
Austria ( because of Halla Hodges )

Region filter sh
These are taken out by Region filter sh
Azad Kashmir
Andhra Pradesh
These are taken out previously by name filter ll
Flintshire ( because of Katell Whitaker )
Kirkcudbrightshire ( because of Yeo Allen )

So if my current understanding of what you want is correct, then my results look correct
So who/ what has got something wrong?

Also see your result for example

Count

Total rows: 100
Name filter: 100 Left / Filtered 0
Region filter: 100 Left / Filtered 0
Country filter: 96 Left / Filtered 4

Fname=

Fregion=

Fcountry= us

That finds 4 rows to remove for only us

So how can you find 8 rows to remove for us here

Count

Total rows: 100
Name filter: 80 Left / Filtered 20
Region filter: 72 Left / Filtered 8
Country filter: 64 Left / Filtered 8

Fname= rr, ll

Fregion= sh

Fcountry= us

I think there are only 4 rows in country column in the table with us So you can only remove maximum of 4 rows. ( It may be 4 or 3 or 2 or 1 or 0 , depending on what full rows got removed by previous filtering... )

( macro used in atttched file, FilTit3.xlsm , is also here: https://www.excelfox.com/forum/showt...ll=1#post23212 )

**sandy666** · 08-24-2023, 06:13 PM

Code:

            total = Table.RowCount(TPH),
            name = Text.From(Table.RowCount(TPH)-Table.RowCount(TSR))&" Left / Filtered "&Text.From(Table.RowCount(TSR)),
            region = Text.From(Table.RowCount(TPH)-Table.RowCount(TSR)-Table.RowCount(TSR1))&" Left / Filtered "&Text.From(Table.RowCount(TSR1)),
            country = Text.From(Table.RowCount(TPH)-Table.RowCount(TSR)-Table.RowCount(TSR1)-Table.RowCount(TSR2))&" Left / Filtered "&Text.From(Table.RowCount(TSR2))

total
total-filtered1
total-filtered1-filtered2
total-filtered1-filtered2-filtered3

**DocAElstein** · 08-24-2023, 06:23 PM

Originally Posted by sandy666

total
total-filtered1
total-filtered1-filtered2
total-filtered1-filtered2-filtered3

I assume you are trying to say
Total rows
Left after name filter1 ( rr ll ) = total-filtered1
Left after region filter2 ( sh ) = total-filtered1-filtered2
Left after country filter3 ( us ) = total-filtered1-filtered2-filtered3

That is not the problem

Problems is:
_1 ) How can you remove(Filtered) 8 lines when filtering out by sh in region column
that is impossible: there are only 4 lines in original table with a sh in region column
_2 ) How can you remove(Filtered) 8 lines when filtering out by us in country column
that is impossible: there are only 4 lines in original table with a us in country column

Originally Posted by DocAElstein

Your results:

Count

Total rows: 100
Name filter: 80 Left / Filtered 20
Region filter: 72 Left / Filtered 8
Country filter: 64 Left / Filtered 8

Fname= rr, ll

Fregion= sh

Fcountry= us

**sandy666** · 08-24-2023, 07:23 PM

it should be a proper result

Count

Total rows: 100

Name filter: 86 Left / Filtered 14

Region filter: 82 Left / Filtered 4

Country filter: 79 Left / Filtered 3

Thread: VBA - Count filtered rows in the table

Thread Tools

Search Thread

Display

Similar Threads

VBA Copy Rows From One Workbook To text csv File Based On Count In Different Workbook. Cross Posted Chaos

Delete Filtered Rows Excluding The Header

Creating A Filtered Subset Of A Data Table Using Controls Instead Of Slicers

Lookup and Count Using Pivot Table

Unique Count on a Filtered Range

Bookmarks

Bookmarks

Posting Permissions