FIND method ignores filtered rows? 2

[VBAjedi]

In XL97, the following find statement ignores rows that have
been hidden by the AdvancedFilter:

x = sh.Cells.Find(what:="*", after:=Range("IV65536&quot, _
searchorder:=xlByRows, searchdirection:=xlPrevious).Row

Anybody know a way around this? The intent of the statement is to find the last row (visible or not) on worksheet object "sh" that actually has cell contents (I'm not interested in cells that have formatting applied but no contents, or in Excels native attempts to track used ranges).

Thanks!

VBAjedi

 

[Chattin]

Check Tom Urtis's first post in this thread - if I've understood you correctly it will be relevant

http://www.mrexcel.com/board2/viewtopic.php?t=66480&highlight=last+cell

HTH

Chris ;-)

 

[xlbo]

why not perform the search BEFORE the filter ???
or, use
x = sh.Cells.Find(what:="*", lookin:=xlformulas, after:=Range("IV65536&quot, _
searchorder:=xlByRows, searchdirection:=xlPrevious).Row

if you use "formulas" it'll find 'em in filtered rows, "values" won't - works for the manual find as well as in code

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[VBAjedi]

Chris,

Tom's code is essentially the same as the code I initially posted. Despite Tom's brusque assertions to the contrary, I am fairly certain (having tried it) that his suggestion does NOT search hidden rows (in XL97, anyway).

Geoff,

You got my hopes up - that sounded like a "makes sense" solution. . . but I can't get it to work (again, using XL97 here). I did a direct copy/paste of your code, but it still returns the last visible row in a sheet where filters (Auto or Advanced) are active. Are you perhaps using a different version?


VBAjedi

 

[xlbo]

yeh - sorry - XP but I'm sure it works in '97.....we only just converted from '97 and it's something that's been in the back of my head for a while......

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[VBAjedi]

What could I be doing wrong, then? The syntax is pretty hard to screw up. . . here's my function:

Function LastRow(sh As Worksheet)
Dim x
x = sh.Cells.Find(What:="*", LookIn:=xlFormulas, After:=Range("IV65536"), _
    SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastRow = x
End Function

VBAjedi

 

[TonyJollans]

Hi All,

I just had a quick play with this and there is a difference between HIDDEN rows and FILTERED OUT rows but I haven't figured out the finer detail of it. Your Find finds hidden rows 'correctly' but completely ignores filtered out rows.

Enjoy,
Tony

 

[VBAjedi]

Ah - that probably explains some of the confusion. Thanks, Tony! Now, do you have a solution or workaround for my original request? It's filtered rows that I need to account for. I had a star halfway out of my pocket for Geoff's post when I discovered it wasn't the answer. . . this thing needs a home!

;^)


VBAjedi

 

[DaleWatson123321]

Hi VBAJedi,

How about this possibility... i.e. that of a) Showing the data, b) running your routine, and c) Filtering the data.

Sub Find_Data()
Application.ScreenUpdating = False
Show_All
<<< Your_Routine >>>
Filter_Data
Application.Goto Reference:=&quot;R1C1&quot;
Application.ScreenUpdating = True
End Sub


Sub Show_All()
'Shows All Data - UnHides Filtered Row(s)
Worksheets(&quot;Database&quot.Select
If Worksheets(&quot;Database&quot.FilterMode = True Then
ActiveSheet.ShowAllData
End If
End Sub

Sub Filter_Data
Worksheets(&quot;Database&quot.Select
Range(&quot;data&quot.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=&quot;crit&quot;, _
Unique:=False
End Sub

I hope this can work. Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[VBAjedi]

Dale:

The issue here is that I'm trying to create a generic function that finds the last row with contents on whatever sheet object is passed to it (i.e &quot;LRow = FindLast(MySheet)&quot. I use AutoFilter and AdvancedFilter a lot, but the specifics are different each time. That would make it very bulky to remove/reapply the filter. Removing filters would be easy, but to be able to reapply them I'd have to have some method for dynamically determining (before removing them) whether filtering was currently applied on the target sheet, the type of filtering applied, the filter criteria, and the filtered range.

Any idea how to accomplish this WITHOUT attempting the above drudgery? I want this function to be small and fast.

Thanks!

VBAjedi

 

[DaleWatson123321]

VBAJedi,

You're right about it being a pain.

What about setting up a &quot;dump&quot; sheet, where you can use Advanced Filter to Extract ALL the records from any given sheet - by using a &quot;blank&quot; criteria to capture ALL records (including the filtered records).

You could have a &quot;resident&quot; COUNT formula (with a name assigned) and then pick up the &quot;count&quot; from it after the extraction is made. This of course can only work accurately IF the field of data being extracted has no &quot;blank&quot; cells.

Hope this can help.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[VBAjedi]

Dale,

That approach might work on a one-off application, but I wanted to develop a function for my standard &quot;toolbox&quot; module that I import into every project I do. The simplicity of a &quot;Find&quot; function that searched rows regardless of whether filters were active or not was appealing, but apparently not to be.

Guess I'll have to continue custom coding the LastLine function each time I need it. Hey, at least I tried.

Thanks anyway to everyone who contributed input and brainpower!

VBAjedi

 

[DaleWatson123321]

VBAJedi,

&quot;Back to the drawing board for me&quot;. :-(

I was working on another &quot;rush&quot; job (regular work) at the same time, and just now realized that my &quot;solution&quot; was &quot;not&quot; - as filtered records are NOT extracted when &quot;all&quot; records are (should be) included via having a &quot;blank&quot; criteria range.

I'll naturally let you know if I come up with a &quot;workable&quot; solution.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

VBAJedi,

It's been a busy day, but one last &quot;kick&quot; at this.


Just to clarify (for anyone who might be &quot;listening in&quot...

The method I had suggested &quot;can&quot; work (perhaps for other situations not as complex as that of VBAJedi's). The reason why it initially failed for me, is because I had a subroutine (pre-existing model) that &quot;Re-Set&quot; the Data range. Thus, in order to capture &quot;all&quot; the records (including filtered records) the &quot;ShowAll&quot; data had to be part of the &quot;Re-Set&quot; routine. But the &quot;ShowAll&quot;, as VBAJedi correctly pointed out, creates a problem.

But here's the &quot;solution&quot; (at least for more simple situations)... Rather than have a subroutine that &quot;re-sets&quot; the data range for the Advanced Filter extraction, I simply eliminated that re-set routine. Instead, I created a &quot;set&quot; range for the range referenced by the Advanced Filter. This &quot;set&quot; range can extend well beyond the range that might be occupied by data - or include the entire column(s).

VBAJedi,

Here's another option that might be worth considering...
Rather than having to extract the data, have a routine that simply re-assigns a named range that is referenced by a DCOUNTA formula. Therefore, wherever this name is re-assigned, that one DCOUNTA formula will generate the number of records in that range. And of course, the &quot;key&quot; here is to assign the range to a range LARGER than the range occupied by data - i.e. assigning the name to an entire column would do nicely.

I hope this might do the trick for you.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[xlbo]

I think I have it by jove

You need to use the special &quot;Filterdatabase&quot; named range. This is created whenever a filter is applied to a range. It is in the standard format of

SheetName!_filterdatabase

So......assuming your data starts in A1,

Function LastRow(sh As Worksheet)
LastRow = range(sh.name&&quot;!_filterdatabase&quot.rows.count
End Function

should work for you

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[TonyJollans]

Hi VBAjedi,

Just a couple of thoughts. I don't, at the moment, have a proper answer, so you don't need to read any further if that's all you want but I felt like crystallising some issues.

Filtered out rows are hidden; there is no special property that marks them out as different from other hidden rows. They are just treated differently depending on whether or not there is a filter in place and whether or not they fall within the filtered range.

Now, a generic function must work whatever the circumstances, and the problem we have is that Find doesn't look in hidden rows when there is a filter on the sheet. Removing and re-applying the filter seems like a solution (no matter how unwieldy) but it suffers from another problem ..

.. when a filter is in place, a row can be manually hidden and if that row is within the filtered range, it will be unhidden when the filter is removed, but not re-hidden when the filter is reapplied.

So we need another solution. The _FilterDatabase named range might be useful but, here again, there are some complications. The range is only part of the sheet - there may be data before or after it - and it may include blank rows.

If there is not a filter in place, the Find provides the desired result. Otherwise, if the result of the Find is a cell after the end of the filtered range, it should be correct.

If the result of the Find is before the end of a filtered range further checks are needed and here's where it gets difficult. All we really know is that there are no non-empty visible rows between the result of the Find and the end of the Filtered Range and there doesn't seem to be much of an alternative to a row-by-row check.

BUT, what is the real purpose of identifying the last cell? Would you really want, on a generic basis, to be using cells within a previously defined filtered range? If not, then you might as well just take the larger of the result of the Find and the end of the filtered range.

Enjoy,
Tony

 

[xlbo]

How about this then tony:

Function LastRow(sh As Worksheet)
If sh.AutoFilterMode = True Then
LastRow = Range(sh.Name & &quot;!_filterdatabase&quot.Rows.Count
Else
Set x = sh.Cells.Find(What:=&quot;*&quot;, LookIn:=xlFormulas, After:=Range(&quot;IV65536&quot, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row LastRow = x
End If
End Function

I know it's fallible if there are rows after the filter but that would be bad spreadsheet design and hence I shall ignore the possibility ;-)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[TonyJollans]

Hi Geoff,

I'd rather see FilterMode than AutoFilterMode.

And, .Rows.Count doesn't allow for rows BEFORE the filtered range (for criteria for example)

But, apart from that ...

Enjoy,
Tony

 

[xlbo]

good spot Tony - I rarely use Advancedfilter so autofiltermode works fine for me - could always test for the existence of the filterdatabase range name anyway but I also take your point re: criteria cells before the filter. It's really annoying that using lookin:=xlformulas works for hidden rows but not filtered ones. I do have a suggestion though - it's basic but should work.......you'll like this


Lastrow = activesheet.usedrange.rows.count

ta daaaaaaa - I know, I know, I wrote a whole FAQ saying not to use usedrange but for this.......I think it's the only way - you can always use
[ActiveSheet.UsedRange.row.count]

to evaluate it and make it recalc anyways......

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Luis939]

Really quickly, I was just looking into this forum and noticed that xlbo said he usually is against using 'usedrange', i do use that so I'd like to know why I shouldn't use it, thanks!