VBA - Count filtered rows in the table

Hi Alan, hope you are well,

so ad rem

I've a table (3 columns, can be more), and for this table I've three filters
I need count rows for each filter (filtered rows and rows left after filtering), eg.

https://i.postimg.cc/V6CDHXj3/screenshot-138.png

there should be possibility resize the main table for more columns and add new tables with filters at the same time (with empty rows that will not be taken into account when counting filtered rows)

the result should be more or less like this:

https://i.postimg.cc/3NQnMW1W/screenshot-139.png

is this possible to do it automatically after add/remove values to/from filter tables?
I don't need filtered table but I need result table like you can see above

thanks

Hi there :)
I am not 100% sure about this, because I have never tried to apply VBA to a filtered table.
But I just did a quick google, and I think we can do things like count the visible cells using VBA, and I also expect applying normal VBA to a filtered table will probably tell us all what is in a table.
It is usually possibly to get a VBA coding to run automatically when something or other is done to a worksheet.
And we can usually get a coding to be flexible/ dynamic and work on as many columns as are ever there at any time.

So I expect what you want could probably be done with VBA, and I expect I could probably figure out how to do it.

I probably would just need to experiment a bit as I never did anything with a filtered table. It’s usually possibly to figure out how to do most things with VBA.

If you give me a small size sample file I will take a look.

Alan

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=oVb1RfcSHLM&lc=UgwTq-jZlZLnLQ5VB8Z4AaABAg.9Hroz-OyWog9tYjSMc1qjA
https://www.youtube.com/watch?v=0pbsf6sox34&lc=Ugxp9JFvvejnqA68W1t4AaABAg
https://www.youtube.com/watch?v=kfQC-sQxMcw&lc=UgyCxQWypNIhG2nUn794AaABAg.9q1p6q7ah839t UQl_92mvg
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgyOh-eR43LvlIJLG5p4AaABAg.9isnKJoRfbL9itPC-4uckb
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugy1B1aQnHq2WbbucmR4AaABAg. 9isY3Ezhx4j9itQLuif26T
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgxxajSt03TX1wxh3IJ4AaABAg. 9irSL7x4Moh9itTRqL7dQh
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgwJAAPbp8dhkW2X1Uh4AaABAg. 9iraombnLDb9itV80HDpXc
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgzIzQ6MQ5kTpuLbIuB4AaABAg. 9is0FSoF2Wi9itWKEvGSSq
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK75iCEaGN
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK7XF33njy
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCSgpAqA1
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCy--3x8E
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwNaJiNATXshvJ0Zz94AaABAg. 9iEktVkTAHk9iF9_pdshr6
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iFAZq-JEZ-
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgxV2r7KQnuAyZVLHH54AaABAg. 9iDVgy6wzct9iFBxma9zXI
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwnYuSngiuYaUhEMWN4AaABAg. 9iDQN7TORHv9iFGQQ5z_3f
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwJ3yzdk_EE98dndmt4AaABAg. 9iDLC2uEPRW9iFGvgk11nH
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgyDWAVqCa4yMot463x4AaABAg. 9iH3wvUZj3n9iHnpOxOeXa
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwvLFdMEAba5rLHIz94AaABAg. 9iGReNGzP4v9iHoeaCpTG8
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iHpsWCdJ5I
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

sure, here is


https://www.youtube.com/@alanelston2330/featured


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htJ6TpIOXR
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htOKs4jh3M
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?p=320960#p320960
https://eileenslounge.com/viewtopic.php?p=320957#p3209573
https://eileenslounge.com/viewtopic.php?p=318868#p318868
https://eileenslounge.com/viewtopic.php?p=318311#p318311
https://eileenslounge.com/viewtopic.php?p=318302#p318302
https://eileenslounge.com/viewtopic.php?p=317704#p317704
https://eileenslounge.com/viewtopic.php?p=317704#p317704
https://eileenslounge.com/viewtopic.php?p=317857#p317857
https://eileenslounge.com/viewtopic.php?p=317541#p317541
https://eileenslounge.com/viewtopic.php?p=317520#p317520
https://eileenslounge.com/viewtopic.php?p=317510#p317510
https://eileenslounge.com/viewtopic.php?p=317547#p317547
https://eileenslounge.com/viewtopic.php?p=317573#p317573
https://eileenslounge.com/viewtopic.php?p=317574#p317574
https://eileenslounge.com/viewtopic.php?p=317582#p317582
https://eileenslounge.com/viewtopic.php?p=317583#p317583
https://eileenslounge.com/viewtopic.php?p=317605#p317605
https://eileenslounge.com/viewtopic.php?p=316935#p316935
https://eileenslounge.com/viewtopic.php?p=317030#p317030
https://eileenslounge.com/viewtopic.php?p=317030#p317030
https://eileenslounge.com/viewtopic.php?p=317014#p317014
https://eileenslounge.com/viewtopic.php?p=316940#p316940
https://eileenslounge.com/viewtopic.php?p=316927#p316927
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

I don’t see any Excel Filters in the worksheet ( These things are what I think are Excel filters: https://support.microsoft.com/en-us/...6-38c37dcc180e ) ?

But I can probably do the whole thing with VBA, and forget all about filters.

So for example..
You type in
rr

Attachment 5511




Then VBA will tell you this
Harrison Irwin
Sierra Howe
Curran Herring
Blake Herring
Jarrod Mccarthy
Or it will tell you anything else , like you asked for, Total rows , how many left etc. etc.

Probably that is what you want anyway, because you said ... I don't need filtered table but I need result table….

So I will make a start and then give you something, then we can take it from there any modify to suit you….

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

there is no excel filter , it is just example
as I said I need count rows not see the filtered rows
the result table shows what I want, no less, no more, depending on the filter values used in the relevant tables


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwTUdEgR4bdt6crKXF4AaABAg. 9xmkXGSciKJ9xonTti2sIx
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwWw16qBFX39JCRRm54AaABAg. 9xnskBhPnmb9xoq3mGxu_b
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9xon1p2ImxO
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgybZfNJd3l4FokX3cV4AaABAg. 9xm_ufqOILb9xooIlv5PLY
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9y38bzbSqaG
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgyWm8nL7syjhiHtpBF4AaABAg. 9xmt8i0IsEr9y3FT9Y9FeM
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9xpn-GDkL3o
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

OK, I understand a bit better what you want, but I probably do not understand fully yet.

This is what I done so far:
Here is a macro https://www.excelfox.com/forum/showt...ll=1#post23204
It is also in the uploaded file. It works automatically if you type anything in column A or C or E ( or F * )

It does not get exactly the results you want, but I don’t understand yet how you got those results: I don’t understand how you get this:
" Total rows: 100
Name filter: 80 Left / Filtered 20
Region filter: 72 Left / Filtered 8
Country filter: 22 Left / Filtered 50 "

---

This is what https://www.excelfox.com/forum/showt...ll=1#post23204 it does:
If you type rr in column A , https://i.postimg.cc/s2G1SXKS/rr.jpg , it tells you automatically this
name Filter by rr will get 6 rows

Pascale Murray
Harrison Irwin
Sierra Howe
Curran Herring
Blake Herring
Jarrod Mccarthy

Total rows in name column was 100

If you type ll in column A it tells you this
name Filter by ll will get 14 rows

Katell Whitaker
Phillip Hunter
Brett Blackwell
Julian Castillo
Kelly Ware
Jin Mcmillan
Yasir Rollins
Yeo Allen
Hilel Marshall
Dillon Dunlap
Jin Gilliam
Petra Sellers
Chancellor Ratliff
Halla Hodges

Total rows in name column was 100

If you type sh in column C it tells you this
region Filter by sh will get 4 rows

Azad Kashmir
Flintshire
Andhra Pradesh
Kirkcudbrightshire

Total rows in region column was 100

If you type u in column E it tells you this
country Filter by u will get 25 rows

Peru
Belgium
Turkey
Turkey
Russian Federation
South Africa
Turkey
United States
South Africa
South Korea
Belgium
Australia
Russian Federation
Belgium
Peru
South Korea
Belgium
Ukraine
Belgium
Belgium
South Africa
Peru
Peru
United Kingdom
Austria

Total rows in country column was 100

---

*If you then add another column in the table, and add another single column like this
Attachment 5512
, and if you then type Big in column F, then it will tell you the truth
alan Filter by Big will get 3 rows

Big Cock
Big Brain
Big Castle

Total rows in alan column was 4
Attachment 5513
:)

---

If you tell me exactly how you get your results, then I can maybe think again, or maybe whatever….

Quote:

---

If you tell me exactly how you get your results, then I can maybe think again, or maybe whatever….

---

sure, I can tell you but you said long time ago you don't know Power Query

here are two original files (source of data: csv file, result file: xslx file)

path to the CSV file is: D:\test\countrows\data-v9i8Rbfh1ul6_1diL56pb.csv , if it will be different, xlsx will wont work
whole line of source is:

Code:

---

Source = Csv.Document(File.Contents("D:\test\countrows\data-v9i8Rbfh1ul6_1diL56pb.csv"),[Delimiter=",", Columns=3, Encoding=65001, QuoteStyle=QuoteStyle.None]),

---

if you extract zip file and move countrows folder to D:\test\ - it should work

(Using Excel 2013). I think I did what you said https://i.postimg.cc/xqv2yB7v/D-test-countrows.jpg


Then I opened the Excel file. ... but, I don’t see anything new
https://i.postimg.cc/8jwgxMTs/test01-xlsx-Filters.jpg https://i.postimg.cc/hfKqCpWH/test01-xlsx-Count.jpg


( The csv file is just the table https://i.postimg.cc/PJBXMWqj/csv-Fi...just-table.jpg )

---

So I still don’t understand yet what it is you want a VBA macro to do

---

Try it differently

Forget VBA
Forget Power Query
Forget Computers.

Now let’s say you do everything yourself manually

I give you this Table: https://i.postimg.cc/2bqX9L2b/I-give...this-table.jpg


Then you give me these results: https://i.postimg.cc/McpLRFBY/You-gi...e-rtesults.jpg


How did you do it? What did you do?

---

I don’t understand how this table, https://i.postimg.cc/4dwkdzc5/I-give...this-table.jpg , can give these results https://i.postimg.cc/bvxjXXN4/You-gi...e-rtesults.jpg ????


Perhaps I just need you to do your post #1, again, in a bit more detail. Tell me exactly what you do so that I know what to make the VBA code do.

For my low level of intelligence, low level of filtering experience, this explanation is not enough…I've a table (3 columns, can be more), and for this table I've three filters
I need count rows for each filter (filtered rows and rows left after filtering),

For example:
How can I have Region filter 72 left / filtered 8 when in table column I ( region ) , I have 100 things to start with??? 72+8 is not 100??

---

---

---

---

I got an idea… let me light a candle in my thinking head, and drink some SchlappenBier medicine:

https://i.postimg.cc/RZQdJf46/Thinki...r-Medicine.jpg


Is this what you mean:
First I filter by Fname using the whole table and this gets me some things “Filtered”. “Filtered” means this: If I filter, for example, by Fname rr then these 6 lines are removed from the table
Pascale Murray
Harrison Irwin
Sierra Howe
Curran Herring
Blake Herring
Jarrod Mccarthy
So first result will be
Name filter: 94 left / Filtered 6

So now I filter by FRegion using the 94 rows left: For example if I now filter by FRegion ll then these 14 lines are removed
Katell Whitaker
Phillip Hunter
Brett Blackwell
Julian Castill o
Kelly Ware
Jin Mcmlllan
Yasir Rollins
Yeo Allen
Hilel Marshall
Dillon Dunlap
Jin Gilliam
Petra Sellers
Chancellor Ratliff
Halla Hodges
So next result will be
Region filter: 80 left: Filtered 14

And so on….

How does that sound?

https://i.postimg.cc/RZV2Fmps/logic.png
each filter filters the values of the remaining rows and not all, only the first filter filters the total (100).
you can test it using only the last filter with the rest left blank (don't know if it will work on xl2013)

https://i.postimg.cc/6qf1Wbrx/screenshot-140.png
https://i.postimg.cc/mZYnyk0p/screenshot-141.png

hope it's clear now ;)

more options

https://i.postimg.cc/Yq2dN3Nj/option1.png

or

https://i.postimg.cc/RVPXgHGd/option2.png

you insisted on showing the filter results and I don't want it, maybe this description will be better: filter1 - show count filtered1 rows AND count left rows after filter1, then filter2 shows count rows from left rows after filter1 - it will be count2 filtered and left2 after filter2 , and etc

Quote:

---

Originally Posted by sandy666

....you can test it using only the last filter with the rest left blank (don't know if it will work on xl2013)....

---

As far as I am aware, nothing does anything on your file if I download it as you said and then open it. But maybe I don’t know what to do to make anything happen if it has something to do with making PQ do something.
But that is not important.
I think I am closer now to understanding . ( Probably some of the problem for me was/ is that I never did anything with filters so I did not know that for example “filter by rr” or “filtered by rr” means “remove all lines in table where in column name, the name contains rr”
- It’s obvious with hindsight, but then usually everything is.)

---

I will have another go and post again later with it.

_ (I will always need the table, the full table, and also the filtered tables as I go along. Maybe the full table and all the others could be put in a spare third worksheet. I can get the full table from the csv file with VBA, but I expect you can get it better with PQ)

_ The table can be any size, any number of rows and columns.

_ You don’t want the full table at all. And I don’t particularly want it on the first worksheet.
I suggest
First worksheet is for filter tables
Second worksheet is Results
Third worksheet has full table and any other shit that my coding would need. (But maybe later we can do without it.**)

But you can suggest something else if you like.
I could get the coding to work on the csv file, and so the full table would never be in the Excel file, but that would be more complicated and it would also make it difficult to develop the coding and check and fix any mistakes initially.
**Maybe later when you are happy that everything is working as you want, then later I could modify the code to work only on the csv file, so then you would never see the full table or filtered tables ever in Excel