+ Reply to Thread
Results 1 to 4 of 4

Code assistance

  1. #1
    Jim May
    Guest

    Code assistance

    The following allows me to "click" within a column of my auto-filter
    and all blank rows (of that column only) are auto-hidden.

    Existing problem(s), however:
    1) It assumes that the AutoFilter is already "on".
    Should probably allow (via code) when autofilter has been turned off
    2) If one or more columns has been previously selected, I would like
    to Unfilter them (remove say field:= 2, or 3) and replace with my newly
    selected field (with nonblanks).

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(ActiveSheet.AutoFilter.Range, Target) Then
    ColSel = Target.Column - 2 'Table starts in Column C
    Selection.AutoFilter Field:=ColSel, Criteria1:="<>"
    End If
    End Sub

    Thanks for any help,
    have already searched extentively, Goolgle
    and all my Books, but No Luck.



  2. #2
    Jim May
    Guest

    Re: Code assistance

    Never mind, I got it... Seem to work as I want it to.
    I added:

    If ActiveSheet.FilterMode Then
    ActiveSheet.ShowAllData
    End If

    after Private... Line
    Jim

    "Jim May" <[email protected]> wrote in message
    news:Abile.18789$Fv.622@lakeread01...
    > The following allows me to "click" within a column of my auto-filter
    > and all blank rows (of that column only) are auto-hidden.
    >
    > Existing problem(s), however:
    > 1) It assumes that the AutoFilter is already "on".
    > Should probably allow (via code) when autofilter has been turned off
    > 2) If one or more columns has been previously selected, I would like
    > to Unfilter them (remove say field:= 2, or 3) and replace with my newly
    > selected field (with nonblanks).
    >
    > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    > If Intersect(ActiveSheet.AutoFilter.Range, Target) Then
    > ColSel = Target.Column - 2 'Table starts in Column C
    > Selection.AutoFilter Field:=ColSel, Criteria1:="<>"
    > End If
    > End Sub
    >
    > Thanks for any help,
    > have already searched extentively, Goolgle
    > and all my Books, but No Luck.
    >
    >




  3. #3
    Bob Phillips
    Guest

    Re: Code assistance



    "Jim May" <[email protected]> wrote in message
    news:Abile.18789$Fv.622@lakeread01...
    > The following allows me to "click" within a column of my auto-filter
    > and all blank rows (of that column only) are auto-hidden.
    >
    > Existing problem(s), however:
    > 1) It assumes that the AutoFilter is already "on".
    > Should probably allow (via code) when autofilter has been turned off


    You just test AutoFilterMode for this

    > 2) If one or more columns has been previously selected, I would like
    > to Unfilter them (remove say field:= 2, or 3) and replace with my newly
    > selected field (with nonblanks).


    Didn't really get this bit Jim. Do you mean if the autofilter is on another
    column, remove it. and in all cases apply it to the new column? If so, I
    don't get the target column -2. Would you not filter the target column?



  4. #4
    Jim May
    Guest

    Re: Code assistance

    My Autofilter Range is C4:G50; so If I click in column F (which is Col 6)
    I must subtract 2 (always) to get my column Number in my range.. (that's
    it)..

    I figured out - and it now works great,,, see above..


    "Bob Phillips" <[email protected]> wrote in message
    news:%[email protected]...
    >
    >
    > "Jim May" <[email protected]> wrote in message
    > news:Abile.18789$Fv.622@lakeread01...
    > > The following allows me to "click" within a column of my auto-filter
    > > and all blank rows (of that column only) are auto-hidden.
    > >
    > > Existing problem(s), however:
    > > 1) It assumes that the AutoFilter is already "on".
    > > Should probably allow (via code) when autofilter has been turned off

    >
    > You just test AutoFilterMode for this
    >
    > > 2) If one or more columns has been previously selected, I would like
    > > to Unfilter them (remove say field:= 2, or 3) and replace with my newly
    > > selected field (with nonblanks).

    >
    > Didn't really get this bit Jim. Do you mean if the autofilter is on

    another
    > column, remove it. and in all cases apply it to the new column? If so, I
    > don't get the target column -2. Would you not filter the target column?
    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1