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

 

[xlbo]

see my FAQ on "Finding the REAL last row"
essentially, usedrange can be inaccurate - especially if you are adding, then clearing data / formats - it doesn't get reset until a save.....

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]

You boys been BUSY!

;^)

Geoff: A star for thinking of using the "FilterDatabase" range. I like the potential that opens up, and used it in my code (below).

Tony: A star for that excellent summary of the potential issues with the proposed approaches. Your concepts also found their way into my code (below).

I agree that there should not be rows after the filter range with data in them, and I am fine with a solution that doesn't check for them. However, the end of the FilterDatabase range is not close enough for me, so I found a more accurate way. I realized that the filters don't actually hide blank rows in the FilterDatabase range if they are after the last row with data in it. So comparing the .Find methods last row with the last hidden row on the sheet gives me the true last row with contents in it! Here's what I came up with:

Function LastRow(sh As Worksheet)
' Returns last populated data row if filters active,
' or last row with contents otherwise
Dim a, b, x, z

z = sh.Cells.Find(What:="*", LookIn:=xlFormulas, After:=Range("IV65536"), _
   SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
If sh.FilterMode = True Then
   a = Range(sh.Name & "!_filterdatabase").Cells(1, 1).Row
   b = a + Range(sh.Name & "!_filterdatabase").Rows.Count
   For x = b To a Step -1
      If sh.Range("A" & x).EntireRow.Hidden = True Then
         If x > z Then z = x ' Only rows with contents are hidden
         Exit For
      End If
   Next x
End If
LastRow = z
End Function

So IF filters are active I trigger the loop. Worst case scenario on the loop (60000+ blank rows in the filterdatabase range after the last hidden row) takes about 2 seconds to process on my computer. Not bad. . .

Any final thoughts? Thanks again for your input! This was a fun one.

VBAjedi

 

[Luis939]

Not to go offtopic, but regarding 'usedrange' ive noticed that if i have something like
set myrange=activeworkbook.activesheet.usedrange.find(what:="mystring&quot

and mystring is in cell A1, myrange wont catch that mystring is in cell A1, instead it catches the next instanc e of mystring,
why does it seem to skip over whatever is in cell A1??

 

[VBAjedi]

Luis,

Unless you specify an "After:" cell, the find method starts looking AFTER the first cell in the active range. So your code starts looking in either B1 or A2 (depending on whether SearchOrder: is set to xlByRows or xlByColumns).

Hope that helps!

VBAjedi

 

[Luis939]

Yes, thank you,

 

[Dawnsie]

Can anyone tell me where to find good examples on filtering using VB. I have a worksheet, where the user selects an item from a combobox and based on the selection, I need to filter the data, copy columns B, C and G whose records match the combox selection (representing column A) into a range on the worksheet. I am fairly new to the Excel/VB side of things and need some fast and serious help with this particular item. Thanks.

 

[SkipVought]

Dawnsie,

Post in a NEW thread, please

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com