Hide and unhide rows based if total column is zero

M

mike_ate_a_pie

Board Regular
Joined
Sep 25, 2009
Messages
69
Hi all,

I'm new to VBA and trying to hide all rows in a profit and loss if the total value is zero.

Is it possible to apply this to several spreadsheets at once in the same workbook.

The total column is AS, and the table is C6:AS55

thanks
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
One way that may work for you is to use the Workbook_SheetActivate event.

Paste this into the ThisWorkbook module:
Code: 
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim arrSheets()
    Dim lngRowNum As Long
    
    Application.ScreenUpdating = False
    
    arrSheets = Array("Sheet1", "Sheet2", "Sheet3")
    If Not IsError(Application.Match(Sh.Name, arrSheets, 0)) Then
        For lngRowNum = 6 To 55
            Rows(lngRowNum).Hidden = Cells(lngRowNum, 45).Value = 0
        Next lngRowNum
    End If
    
    Application.ScreenUpdating = True
    
End Sub

Change arrSheets to the names of the desired sheets....
 
Upvote 0
cheers this was just what i'm looking for. It works great but does slow my spreadsheet down every time you click on a tab with the macro in. Is there anyway to stop this too?

Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Toggle code to hide and unhide columns based on text in a row
Replies
1
Views
446
Cubist
Cubist
V
  • Solved
VBA Assistance - Hide / Unhiding a sheet based on cell contents
Replies
5
Views
296
V L
V
V
  • Question Question
VBA to Hide/Unhide a Tab Based on Contents of Another Cell
Replies
5
Views
297
sxhall
S
H
  • Question Question
VBA code to hide and unhide all the Slicers in workbook.
2
Replies
10
Views
972
Domenic
D
Y
  • Question Question
How to automatically hide/unhide columns based on related month with VBA
Replies
1
Views
308
ysfuyar
Y

Forum statistics

Threads
1,223,243
Messages
6,170,971
Members
452,371
Latest member
Frana

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top