Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Data_filter_autofilter

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Andy Devine
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    Surrey, United Kingdom
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Andy Devine
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #4
    Board Regular
    Join Date
    Apr 2002
    Location
    Midlands, UK
    Posts
    217
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #5
    Board Regular
    Join Date
    Apr 2002
    Location
    Surrey, United Kingdom
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •