Data_filter_autofilter

Andy D 2002

Board Regular
Joined
Apr 22, 2002
Messages
106
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?
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top