Assistance for VBA code to hide Column C and filter Column D

brianv

Board Regular
Joined
Dec 11, 2003
Messages
100
I have a parts list and i need a method to hide Column C and filter Column D5 and below (my quantities are entered in Column D) upon clicking cell B3.

Then upon clinking again cell B3, re-show (un-filter) all columns and rows.

I have something already similar in for another worksheet.... but i cant recall how to make work on multiple columns:

This one works by clinking in C3 to filter Column B only, and then by clicking again in C3, will unfilter. There is no physical button in the cell.
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Const COL_VISIBLE = 10
    Const COL_HIDDEN = 0
    Dim lngCol As Long, _
        lngRow As Long, _
        i As Long
 
    'ignore clicks outside the target range
    If Intersect(Target, Range("C3")) Is Nothing Then Exit Sub
    lngCol = Target.Column
    lngRow = Range("C65536").End(xlUp).Row
 
    'show and hide columns and rows
    If Cells(1, lngCol).Value = "Show" Then
        For i = 2 To 2
            Columns(i).ColumnWidth = COL_VISIBLE
            ActiveSheet.AutoFilterMode = False
            Cells(1, lngCol).ClearContents
        Next i
    Else
        For i = 2 To 3
            If i = lngCol Then
                Columns(i).ColumnWidth = COL_VISIBLE
                Cells(1, lngCol).Value = "Show"
            Else
                Columns(i).ColumnWidth = COL_HIDDEN
            End If
            'With Range(Cells(5, lngCol), Cells(lngRow, lngCol))
            With Range("B3:B" & lngRow)
                .AutoFilter
                .AutoFilter Field:=1, Criteria1:="<>"
            End With
        Next i
    End If
End Sub

what do I modify to make this occur... this original code was written in 2006.

Thanks
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Watch MrExcel Video

Forum statistics

Threads
1,109,043
Messages
5,526,423
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top