Auto Adjust 2 preceeding and 2 succeeding columns...as Cursor moves

[chiswickbridge]

My Table is from A10 to CZ9999, large amount of data and sorted n filtered as per requirement.

In the Filtered mode, column width of Columns A:I is fixed.

Now, while, scrolling across a Row.... Columns J:K are fixed, but need to be adjusted when the Cursor is in Column L..

Adjusting of Column Width must start when the cursor is in Column L

1) Cursor in Column L.....Col.Width J:K=Autofit, Col.Width M:N=Autofit, Col.Width O:CZ=0
2) Cursor in Column M.....Col.Width J:=0, Col.Width K:L=Autofit, Col.Width N:O=Autofit, Col.Width P:CZ=0
3) Cursor in Column N.....Col.Width K:=0, Col.Width L:M=Autofit, Col.Width O:P=Autofit, Col.Width Q:CZ=0
4) Cursor in Column O.....Col.Width L:=0, Col.Width M:N=Autofit, Col.Width P:Q=Autofit, Col.Width R:CZ=0

 

[shknbk2]

Put this in the code for the sheet with the table:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Select Case Target.Column
        Case 12 'Col L
            Columns("J:K").AutoFit
            Columns("M:N").AutoFit
            Columns("O:CZ").ColumnWidth = 0
        Case 13 'Col M
            Columns("J:J").ColumnWidth = 0
            Columns("K:L").AutoFit
            Columns("N:O").AutoFit
            Columns("P:CZ").ColumnWidth = 0
        Case 14 'Col N
            Columns("K:K").ColumnWidth = 0
            Columns("L:M").AutoFit
            Columns("O:P").AutoFit
            Columns("Q:CZ").ColumnWidth = 0
        Case 15 'Col O
            Columns("L:L").ColumnWidth = 0
            Columns("M:N").AutoFit
            Columns("P:Q").AutoFit
            Columns("R:CZ").ColumnWidth = 0
        Case Else
            Columns("J:CZ").AutoFit
    End Select
End Sub

 

[chiswickbridge]

Thank you...Just as I wanted.....

 

[Peter_SSs]

Thank you...Just as I wanted.....

Just checking: Are you sure?

With that code, you do not always get the same result when selecting a cell in the same column.

Example, select these cells in this order and note the results
Select H1 (J is visible)
Select N1 (J is visible)
Select M1 (J is hidden)
Select N1 (J is hidden)

Are those two different results in bold for the same cell selected what you want?

 

[Peter_SSs]

If you don't want that difference in behaviour, you could test to see if this does what you want.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Columns("J:CZ").AutoFit
  If Not Intersect(ActiveCell, Columns("L:O")) Is Nothing Then
    Range(ActiveCell, Range("CZ1")).ColumnWidth = 0
    ActiveCell.Offset(, -2).Resize(, 5).EntireColumn.AutoFit
    If Not Intersect(ActiveCell, Columns("M:O")) Is Nothing Then ActiveCell.Offset(, -3).ColumnWidth = 0
  End If
End Sub