Complex filtering of a table

[CMOT Dibbler]

I need to filter a table but I need to do that based on the values in several cells, not a simple "=red" or ">1".


The basic rule I need will contol 3 cells...

If InvDate & InvNo are not blank but PaidDate is blank then Dont hide

But this needs to account for multiple groups of the 3 cells (as columns) & multiple rows


It could filter Columns first in the groups of 3 then the rows to clear out unwanted columns & then rows in 2 passes?


I've attached a proxy file to try to explain what I need to do. I would give you my attempts at code but I'm not sure where to start.

The real version has 30 groups of 3 columns & thousands of rows, so I figure filter will be the best solution due to speed (feel free to tell me I'm wrong & why).

[JohnTopley]

Please Login or Register  to view this content.

Output on Sheet1

[jindon]

To Sheet4 code module.

Please Login or Register  to view this content.

[CMOT Dibbler]

Thankyou to both of you for taking the time to make a working example.

I'm off to play with your code to understand how it works as I can't immediately follow either solution (but will be unpicking both of them over the next few hours).

[JohnTopley]

Thank you for the rep and please come back if you need further help.

[jindon]

CMOT Dibbler,
You are welcome and thanks for the rep.

You would notice that they work differently, so I have posted my solution.

[CMOT Dibbler]

Please can I ask a few quick questions

I apologise if any of this is a bit obvious, I’ve tried to google what I couldn’t understand & have shortened my questions by quite a bit, but these I couldn’t resolve...



With Me.ListObjects(1)

Why didn't you use the table name? is there a specific reason?


If (a(i, ii) <> "") * (a(i, ii + 1) <> "") * (a(i, ii + 2) = "") Then

You're using * where I would have used and. Again is there a specific reason?


dic(0)(CStr(.ListRows(i - 1).Range(1).Value)) = Empty: flg = True

I'm a bit I'm confused by Range(1), where has that come from? I'm sure it's the current group of 3 columns, but I wondered how it knew what it was.


If dic(0).Count Then

what is it doing here? I can see it's an if to trigger removing the rows, But in the example it's storing 4, (It appears to be the number of rows to keep?) what is the if comparing that 4 against?


.Range.AutoFilter 1, dic(0).keys, 7

I can see its autofiltering using the keys in the Rows Dictionary, but what's the 7?

[jindon]

1) You can change it to table name, if you like.
2) Again, you can change "*" to "And", if you like.
3) ListRows(1).Range(1) refers to First column in first data row. Range(2) = second column..etc.
Unlike Range on the worksheet, Range on the ListObject works differently.
4) dic(0) is storing the Col.A value that should be filtered (should remain).
So, when no data in dic(0), no need to filter the rows.
5) 7 is the constant for xlFilterValues.

HTH.

[jindon]

I noticed a contradiction for 4)...
If dic(0).Count = 0, no data to be filtered (all rows need to be hidden), so the code should look like.

Please Login or Register  to view this content.

[CMOT Dibbler]

The additional answers have helped a lot. Today has been another leveling up day

Thanks again for taking the time to help.

[CMOT Dibbler]

Thank you for the rep and please come back if you need further help.

Thanks again for taking the time to post your code. It was very appreciated.

I've gone through your code after I went through jindon's code & yours obviously works in a very similar way but without using the dictionary.

The copying to sheet 1 is a very useful & timely addition. I need to filter the data & copy it to another sheet in the real worksheet, so that was an inclusion which will definitely get used.

[jindon]

The main difference is to hide columns like your example...

[CMOT Dibbler]

I've hit a snag.

Even if I preclean the table to reduce the number of rows required I'm getting an error.

a = .Range.Value

It's erroring with Run-time error 6 Overflow

The live table is currently at 84 columns & 1000 rows when cleaned.

Is there a way out of this? Or do I have to use another methodology?

[AliGW]

You might want to remove the SOLVED tag for now, then.

[romperstomper]

Most likely you have some cells that are formatted improperly (they probably appear as ###### in the worksheet). Try using:

Please Login or Register  to view this content.

[CMOT Dibbler]

Most likely you have some cells that are formatted improperly (they probably appear as ###### in the worksheet). Try using:

Please Login or Register  to view this content.

Thanks for taking the time to post.

I can't get to it at the moment, but I'll try changing the code tomorrow & report back.

All calculations are done in the forms & the main table is just a repository, so I don't think that's the case.

The cells all contain stings copied & Pasted in from the main table (so no formula), but there may be some special char hiding in a "blank" cell. I've had that issue before.

[CMOT Dibbler]

Sorry for the delay in posting, the role I was hired to do took over for a while

It's now all working & it was dirty cells causing the issue, so thanks to rorya for the hint that got me to look in the right direction.

If anyone is struggling with a similar issue rollis13 helped out a while ago with the code to clean up any "blank" but dirty cells & thats how I got it to work. that code is here...

https://www.excelforum.com/excel-pro...ank-cells.html