VBA - Count filtered rows in the table

**sandy666** · 08-20-2023, 05:23 AM

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.

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:

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

**DocAElstein** · 08-21-2023, 12:38 AM

Hi there

**sandy666** · 08-21-2023, 12:50 AM

**DocAElstein** · 08-21-2023, 10:20 AM

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

rr.JPG

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

**sandy666** · 08-21-2023, 12:05 PM

**DocAElstein** · 08-21-2023, 02:42 PM

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
BigAlan.JPG
, 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
Alan Big Cock Big Brain Big Castle.JPG

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

**sandy666** · 08-21-2023, 08:12 PM

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

**DocAElstein** · 08-21-2023, 11:40 PM

(Using Excel 2013). I think I did what you said

Then I opened the Excel file. ... but, I don’t see anything new

( 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:

Then you give me these results:

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:

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?

**sandy666** · 08-22-2023, 03:46 AM

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)

hope it's clear now

more options

or

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

**DocAElstein** · 08-22-2023, 11:45 AM

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