CODEWITHSUNDEEP
excelvba
Milton
Milton

Reputation: 1

Hide empty Columns to right based on filter

I am trying to set up a worksheet that has a lot of empty cells and many columns.

Basically, I want to filter a column, and based on the filtered value, hide the columns which have a blank cell in the corresponding row that was filtered.

I have an example below. We will call the sheet "task list"? So basically if I filter column B for Eric, Column C, E, G, H, and I should be hidden. Then un-filtering should un-hide these columns.

I'm not too experienced with VBA, but it seems that it may be a worksheet vba based solution?

Example Thanks guys.

Upvotes: 0

Views: 651

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

This assumes that you have no data below the filtered data.

First setup the filter and then run:

Sub HideStuff()
    Dim i As Long
    Columns.Hidden = False
    For i = 3 To 9
    If Application.WorksheetFunction.Subtotal(3, Columns(i).Cells) = 1 Then
        Columns(i).Hidden = True
    End If
    Next i
End Sub

NOTE:

We test against 1 because the only visible data (in a candidate column) should be the header
This can also be easily modified to operate on an event.

Upvotes: 1

Related Questions