Hide columns with zero or blank after filter

[Accountant88]

Hi

I have a spreadsheet with so many columns and rows. I am looking for a macro to filter the product in Column A, cell 2; then hide any columns in the range from BU to CW with zero value or no value. Can anyone help please?

 

[Accountant88]

I am thinking if I filter the product I want first, then press the macro button to hide any columns that with zero value or no value after the filter. The following codes work but I don't know how to make it perform looping until the last column is checked. Please help.

Sub HideColumnsWhenAllRowsAreZero()

Dim Lastrow As Long

Lastrow = Range("BU" & Rows.Count).End(xlUp).Row 'finds the last row for column BU

If WorksheetFunction.Sum(Range("BU1:BU" & Lastrow)) = 0 Then 'sum the values of the column if it equals 0 then move on to hide column

Range("BU1:BU" & Lastrow).EntireColumn.Hidden = True 'Hides column

End If

End sub

 

[SQUIDD]

Hi

maybe try this below

Sub HideColumnsWhenAllRowsAreZero()
For A = 73 To 101
    If WorksheetFunction.Sum(Range(Cells(1, A), Cells(Cells(Rows.Count, A).End(xlUp).Row, A))) = 0 Then Columns(A).EntireColumn.Hidden = True
Next A
End Sub

Dave

 

[Accountant88]

Hi

maybe try this below

Sub HideColumnsWhenAllRowsAreZero()
For A = 73 To 101
    If WorksheetFunction.Sum(Range(Cells(1, A), Cells(Cells(Rows.Count, A).End(xlUp).Row, A))) = 0 Then Columns(A).EntireColumn.Hidden = True
Next A
End Sub

Dave

Brilliant, thanks so much Dave.

 

[Accountant88]

Hi

maybe try this below

Sub HideColumnsWhenAllRowsAreZero()
For A = 73 To 101
    If WorksheetFunction.Sum(Range(Cells(1, A), Cells(Cells(Rows.Count, A).End(xlUp).Row, A))) = 0 Then Columns(A).EntireColumn.Hidden = True
Next A
End Sub

Dave

Hi again Dave,

It seems to work if I select the first product from the filter, after I unhide all the columns hidden from the first filter, it doesn't work. I want to upload a mini-sheet but don't know how!! Can only upload the two images. Hope you don't mind helping me again.

I have amended the code as A=2 - 46

 

[Peter_SSs]

I want to upload a mini-sheet but don't know how!!

At what point of this do you come unstuck?

 

[SQUIDD]

Hi

It would be my guess actually that my code did not work from the start, unless by pure luck.

I believe now i understand what you are trying to achieve.

The code i posted will not work after a filter as it is still checking the whole column, not the filtered column.

I will look at it a bit later, but this reply by me now will bump the thread for you in case any one else wants a go.

Dave

 

[Accountant88]

At what point of this do you come unstuck?

Hi I downloaded the add-in followed the instructions to add the add-in, but it still won't allow me to upload the file

 

[Accountant88]

Hi

It would be my guess actually that my code did not work from the start, unless by pure luck.

I believe now i understand what you are trying to achieve.

The code i posted will not work after a filter as it is still checking the whole column, not the filtered column.

I will look at it a bit later, but this reply by me now will bump the thread for you in case any one else wants a go.

Dave

Thanks in advance Dave.

 

[Peter_SSs]

but it still won't allow me to upload the file

Do you get an error message?