Re: Using CriteriaRange to exclude blank cells in a Range with AdvancedFilter XLS2003 SOLVED
… Don’t hold your Breath… but I think the mad lad may have got it at last….
.AdvancedFilter with Formula Criteria and Crazy Range Referencing
Aka. “.Advancedally Filtering” With offset Row criteria
. This ( I think ) is how the VBA Advanced Filter
“works” in an example such as mine:
a) - VBA “
goes sequentially through” each line of the Range to be “.
Advancedally Filtered” (In my example the Range was A1:B10
b)- (
First Row is reserved for headings. An important point but not too relevant to my “problem”. In the simplest form an usage of the Advanced Filter it is important that Headings in the Range to be “.Advancedally Filtered” and the range for the Filtered Output and the criteria range must have the headings exactly spelt the same. In the use of the formula in the criteria range a heading should still be there. It’s actual value however loses it’s significance and should, in fact, not be the same as any of the other Range headings )
c)- So let’s talk about this sequential progression through my rows. We will call them Hitlerations in honor to my adopted Fatherland. . (In my case there are 9 Hitlerations (
not 10, - we start at row 2 because row 1 is used for headings )
d)- At each Hitleration all the criteria is checked.
e)- Conventionally, and in all sane applications, a criteria formula is referencing cells in the original Range to be “.
Advancedally Filtered” for and in each of the rows. So for all normal and sane application it would be adequate to syntaxly except just a column reference. In my example a B would have been logically thought to be sufficient. This would then apply by every Hitleration to the column of the cell in the row of that Hitleration.
f)- Unfortunately the convention is to follow the Letter for the Column by a number equal to that for the first row in the Range to be “.
Advancedally Filtered” (
In my case 2** ) . This convention resulted in me coming up with the idea to change it, and see what happens.
Unfortunately…
g) - At each Hitleration (all) the criteria is checked. Choosing for example to use B4 rather than B2 (
My Range to be “.Advancedally Filtered” was A1:B10, so Reserving / using Row 1 for the headers would conventionally have _2 in a criteria formula**) means that the criteria is actually checked out in the row which is 2 rows up in the Range to be “.
Advancedally Filtered” .
But importantly the B cell that will be selected for the Filtered output Range will be that in the Hitleration Row. In the case of the first Hitleration 2.
h) - Every subsequent Hitleration will again return the appropriate value from the row of the Hitleration,
if the criteria is met two rows up in the Range to be “.
Advancedally Filtered” . This clearly explains my experimental results better than my dopey idea of the Range to be “.
Advancedally Filtered” being pulled two rows down!!. ( I blame A.Einstein at this point, for sticking this mad idea in my head that everything is relative, ( relatively speaking ) ). This all easily and fully explains my results in Code Criteria 1
i) - For my
Code Criteria 2 using 2 criteria cells the results are simply a combination of Code Criteria 1 and a normal formula ( that for getting at name 8 )
- The big mis – understanding on my behalf is that Rory and pgc were explaining very clearly my crazycriteriacrazy code 2 and I was referring to my criteria code 2, which was my experiments changing the B? from B2 through to B10 at the start of Post # 24 , rather than my crazy criteria crazy experiments later in that post with the B4 = name 10 and B2 = name 10 to get correspondingly out name 8 and name 10 (The problem as always was that I make my Threads too long and therefore too difficult to read. ( I blame Peter_ss for his absence for that
#### )
J) - Coming back to my code criteria 3. The “mystery” is (
was:- ) the missing name 8 after B? = name 10 exceeds B4. If we understand the theory up until now, (If not go back now to the start and read again……. Ready?....... OK: - …….
. …….. The criteria B2 = name 8 returns name 8 when the formula is applied to the 7th Hitleration which is that for row 8 (remember first Hitleration is for row 2 as row 1 is used for headings ). After B_ exceeds B4 this Hitleration don’t work as the offset goes 3 bits up or more which is out of the Range to be “.
Advancedally Filtered” . I couldn’t and can’t physically see or experience this error / formula failure, so totally missed it. - Luckily people who know told me or in the case of pgc could prove it with the formula mod which I applied in criteria code 4 , post # 28 – there the error for B? = name 10 is suppressed allowing the formula to still return name 8 from the B2 = name 8 part. Again I missed this initially as Rory and pgc were referring to the last experiments in the Thread. ( the crazycriteriacrazy codes and experiments not the initial criteriacode codes and experiments from the beginning of the Thread. )
k) - The
Final problem from post #24 with my getting name 8 and name 10 using the criteria of B4 = name 10 and B2 = name 10 is now obvious to me. As clearly explained and demonstrated to me, the problem lies with B2 getting at name 10 with the B2 = name 10 part as at the 9
th Hitleration the B4 = name 10 part causes the whole formula to crash. Again this is overcome by pgc error suppressor.
.
. So that Is it. ( I think)….
. As noted the problem lies partly with Dr. A.Einstein for distracting me with his crazy Theories, relatively speaking.
. The problem also lies with Peter_ss who has not in recent months been there to ignore my normal Threads or to warn me for my long rambling ones.
I only hope Peter_ss is OK and just taking a well earned rest. I hope he comes back soon. His steadying influence ( not too mention his great codes and some amazing formulas ) are greatly missed. - In the last months I have been posting some ridiculously long Threads and really insanely long codes in the code windows which I fear could ultimately crash the forum Software. ( In addition it would be nice if he came back and took care of the “About the Board” Sub Forum , where since his absence many important questions have gone unanswered.
. ( If it helps to bring you back Peter_ss, I will sacrifice myself and promise to leave ( only returning when specifically asked to answer threads ) )
Alan Elston
Bavaria.
