Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Mar 2001
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Autofiltering macro (Excel 2000)

    I would like to write a macro that would perform an autofilter using ">0" as its parameter (I perform this operation manually countless times each day). It would seem to be a pretty straightforward task, but I don't know how to allow the user to select the column to be operated on.

    Any ideas?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Autofiltering macro (Excel 2000)

    It really depends on the context your macros is running in and what other operations it performs. Or is it just a single operation to autofilter the sheet ?. If so why bother with a macro. However if a macro is called for and you have some interaction with the user you could have it apply to the active column, i.e. the column with the selected cell. Or you could use an input box to ask your user to select the column.<pre> FilterColumn = Application.InputBox(prompt:= _
    "Select a Column to filter", Type:=8).Column</pre>

    The above should return the column number of the selected column.

    Otherwise you might need to provide more detail sof what else is happening in your macro.

    Andrew C

  3. #3
    New Lounger
    Join Date
    Mar 2001
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofiltering macro (Excel 2000)

    Thanks Andrew
    It is as you guessed, a single operation. But I prefer using a shortcut key in order to avoid all the mousing. That is the reason for a macro.

    I'd like to have the macro act on the active column, but I'm not sure how to do that. Would you like to show me?

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Autofiltering macro (Excel 2000)

    If you data range starts in column A, the following line of code should help :<pre> ActiveCell.CurrentRegion.AutoFilter Field:=ActiveCell.Column, Criteria1:=">0"</pre>

    If your range is offset from column A you might want to take that into account. To remove the filter you could use<pre> Selection.AutoFilter</pre>

    Hope that helps.

    Andrew C

  5. #5
    New Lounger
    Join Date
    Mar 2001
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofiltering macro (Excel 2000)

    Thank you Andrew

    I think you've inspired me to try to park my mouse and learn VBA.

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Autofiltering macro (Excel 2000)

    Krossano, sounds like a useful macro to have around. Here's Andrew's suggestions in macro form. The AFon autofilters the column that contains the currently active cell with a criteria of ">0". You will want to place these macros into your personal macro workbook. If you need help, just holler. HTH --Sam
    <pre>Option Explicit
    Sub AFon()
    With ActiveCell
    .AutoFilter
    .AutoFilter Field:=.Column, Criteria1:=">0"
    End With
    End Sub
    Sub AFoff()
    Selection.AutoFilter
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

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