Data_filter_autofilter

[Andy D 2002]

hi am using Data_filter_autofilter on a list in column A e.g cells A10 - A100 contain day of the week

what do i need to do to show the my selection?

e.g if i've selected monday, how do i get monday to appear in, say, cell B1?

[Nibbles]

Hi Andy,

Not quite sure what you mean... If you are simply using autofilter on a column of data, you just click on the down arrow which will be displayed in the first cell of your column. You then select what data you want displayed and hey presto. Or are you trying to do this through VBA?

Nibbles

[Andy D 2002]

hi thanks for reply...just want my selection to be shown in a cell (probably is VBA) so if i choose e.g. monday from entries in cells A5:a100 then monday will appear in cell B1, if i choose tuesday then tuesday will appear in cell B1 etc
thanks
Andy

[Iain Lewis]

Hi Andy,

How about this :

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Range("b1").Value = Range("A65536").End(xlUp).Value
End Sub

You will need to put it in the sheet module - let me know if you don't know how.

It looks at the last visible value in column A, so if you have anything below the filter range ( eg a total ) then it'll need changing slightly.

[Nibbles]

Just saw Iain's reply which is nice and compact. But just incase anyone else wants to use autofilter through VBA, or you want to eliminate the problem with having more data below your list in column A, you could use this in a module:

Sub use_autofilter()

' Specify the day to filter and specify the range of your data
DayToGet = ActiveSheet.Range("D1").Value
FinalRow = Range("A15000").End(xlUp).Row
ActiveSheet.Range("A1:A" & FinalRow).Select

' Turn on AutoFilter, if it is not on
If ActiveSheet.AutoFilterMode = False Then Selection.AutoFilter

' Filter the data to just this day of the week
Selection.AutoFilter Field:=1, Criteria1:=DayToGet

' Select visible cell and copy to Cell B1
DaySelection = Range("A15000").End(xlUp)
ActiveSheet.Range("B1").Value = DaySelection

' Turn off the Autofilter and go back to cell A1
Selection.AutoFilter
ActiveSheet.Range("A1").Select

End Sub

THis assumes the information you want to lookup, you type into cell D1. The output is then sent to cell B1. You could run this from a button next to D1 maybe?

Nibbles