PDA

View Full Version : [SOLVED] Find value of cell after filtering



Glaswegian
08-29-2014, 04:23 AM
Hi again

Another Friday problem - I have a list of data in the range B9 to L100 and users are allowed to filter the list (dates in col B) by choosing a month from a userform. This all works fine.

However, I need to find the value of L10 (or what is the equivalent of L10 after filtering) and this is where I have a problem.

The filtering is part of a Print routine that prints out the filtered list. The piece of code that does not work is

With Sheets("Statements")
.AutoFilterMode = False
.Range("B9:B100").AutoFilter Field:=1, Criteria1:=myText, Operator:=xlFilterDynamic
.Range("L9").Value = .Range("L9").Offset(1, 0).SpecialCells(xlCellTypeVisible).Value
End With

Something to do with my use of SpecialCells? I've tried some other combinations but no luck.

There are various other operations and functions within the workbook as a whole but this is the only bit I cannot work out.

Any help greatfully appreciated.

Bob Phillips
08-29-2014, 05:06 AM
If L10 is not visible, it won't be returned in SpecialCells.

What exactly do you mean by 'or what is the equivalent of L10 after filtering'?

Glaswegian
08-29-2014, 06:07 AM
Hi xld

Hmmm....I need to return the value of the cell that, after filtering, is immediately below L9. Without filtering it would, of course, be L10, but now it might be L15 or L45 etc. The cell will be visible though.

Does that help?

Bob Phillips
08-29-2014, 07:48 AM
Iain,

Try this


.Range("L9").Value = .Range("B9:B100").SpecialCells(xlCellTypeVisible).Areas(2).Cells(1,11).Address

Glaswegian
08-29-2014, 08:04 AM
Nice one xld - now I have the address!

many thanks, as always.

Bob Phillips
08-29-2014, 08:23 AM
Sorry, that was just my testing, you can replace Address with Value (but you knew that :))

Glaswegian
08-29-2014, 08:39 AM
Lol - even I worked that out!