PDA

View Full Version : [SOLVED] Excel VB to hide an entire row based on sheet name match and cell dynamically.



spittingfire
04-10-2018, 03:25 PM
Hi All,

I have attached a sample workbook.

What I'm looking to do in the sample workbook is I have 3 sheets. One sheet is Sales SOD, one is Sales and the other is Sales Bilingual.

What I want to do in the Sales SOD sheet I will like to hide a row dynamically if certain conditions are matched.

Each name in Sales SOD will have a matching sheet with the same name.

So for Sales Bilingual in the Sales SOD sheet I want to hide that row if cell AE56 in the Sales Bilingual sheet = 0

Since I have many SODs with many sheets I will like to do this dynamically. All matches will however be based on cell AE56 of the individual sheet name.

Any assistance would be appreciated.

Paul_Hossler
04-10-2018, 03:49 PM
Try this in the 'Sales' worksheet code module




Option Explicit

Private Sub Worksheet_Activate()
Dim rSheetNames As Range, rSheetName As Range

Application.ScreenUpdating = False

With Me
Range(.Cells(6, 1), .Cells(.Rows.Count, 1)).Rows.Hidden = False
Set rSheetNames = Range(.Cells(7, 1), .Cells(.Rows.Count, 1).End(xlUp))
End With

For Each rSheetName In rSheetNames.Cells
On Error GoTo NextSheet
If Worksheets(rSheetName.Value).Range("AE56").Value = 0 Then
Me.Rows(rSheetName.Row).Hidden = True
End If

NextSheet:
Next
On Error GoTo 0
Application.ScreenUpdating = True
End Sub




and this in the ThisWorkbook code module



Option Explicit

Private Sub Workbook_Open()
Worksheets("Sales SOD").Select
End Sub

spittingfire
04-10-2018, 04:14 PM
Thanks Paul,

That is awesome and worked well. Much appreciated.

Paul_Hossler
04-10-2018, 06:33 PM
Glad

BTW, I made a error in the instructions (Worksheet_Activate should be in the Sales SOD code module, not Sales)

The attachment was right, so I hope you used it as an example

spittingfire
04-10-2018, 06:48 PM
Thanks for the info and yes I did use the example from the attachment.