![]() |
![]() |
|
|||||||
| Excel Questions All Excel/VBA questions - formulas, macros, pivot tables, general help, etc. Please post to this forum in English only. |
![]() |
|
|
Thread Tools | Display Modes |
|
|
#1 |
|
Board Regular
Join Date: Apr 2002
Location: Andy Devine
Posts: 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?
__________________
Andy Devine |
|
|
|
|
|
#2 |
|
Board Regular
Join Date: Apr 2002
Location: Surrey, United Kingdom
Posts: 75
|
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 |
|
|
|
|
|
#3 |
|
Board Regular
Join Date: Apr 2002
Location: Andy Devine
Posts: 106
|
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
__________________
Andy Devine |
|
|
|
|
|
#4 |
|
Board Regular
Join Date: Apr 2002
Location: Midlands, UK
Posts: 217
|
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. |
|
|
|
|
|
#5 |
|
Board Regular
Join Date: Apr 2002
Location: Surrey, United Kingdom
Posts: 75
|
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 |
|
|
|
![]() |
| Bookmarks |
| Thread Tools | |
| Display Modes | |
|
|