Code assistance

[Jim May]

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.

 

[Jim May]

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

 

[Bob Phillips]

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?

 

[Jim May]

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..