Worksheet_SelectionChange if entire column is selected

[Jim McCarthy]

I have a sheet in which Cols A, B, C & D each has a macro that toggles autofilter ON/OFF based on the criterion selected for each column.

In the same sheet, columns M and N each contain a series of data and calculations. I sometimes need to manually copy the entire ccontents of one or both columns into adjacent columns (say copy col N to cols O, P & Q). However, if I forget to turn autofilter for col A, B C or D off before I copy and paste, I end up pasting only the filtered cells rather than the entire column contents, as intended.

What I need is an event handling subroutine that aurtomatically turns off autofilter when I select col M and/or N. I have played around with worksheet_SelectionChange but, being a VBA novice, have had no luck.

I would appreciate your help.

Thanks.

 

[Von Pookie]

Try

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'if selected cell is within column M or N
If Not Intersect(Target, Columns("M:N")) Is Nothing Then
    'run code
End If

End Sub

Edit: Nevermind--I missed the "entire column" part of that, somehow My code will run when you select a single cell in either column, which isn't what you're asking.

 

[al_b_cnu]

Hi,

Try this selection change event:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Rows.Count = Rows.Count _
And (Target.Column = 13 Or Target.Column = 14) Then ActiveSheet.AutoFilterMode = False
End Sub

 

[Jim McCarthy]

Thanks, Alan, so simple & worked like a charm.

Thanks also to Kristy.