Unhide/Hide a range of Rows on Another Worksheet Based on Cell Values

[Sanchez88]

Hello to anyone willing to help me,

There's many threads on this but none that help my case (which might be simple but i'm not familiar enough with VBA to do it myself.)

I want the Macro to Unhide Rows based on two different Cells in another worksheet.

For example:

Sheet 1 E41 = 1
Sheet 1 F41 = 5

Sheet 2 Rows (x:x) Unhide
Sheet 2 Rows (y:y) Hide

To clarify i have my version (which has not been working) below.

I've tried "Dim" as Integers too and it hasn't worked.
Any help would be greatly appreciated, i have no other ideas...

Private Sub CommandButton2_Click()

    Dim i As String
    Dim j As String
    Dim k As String
        
    i = ActiveSheet.Cell(E41).Value + 8
    j = ActiveSheet.Cell(F41).Value + 8
    k = ActiveSheet.Cell(F41).Value + 9
    
    Worksheets("Caller 1").Rows("i:j").Hidden = False
    Worksheets("Caller 1").Rows("k:38").Hidden = True


End Sub

 

[rpaulson]

not 100% sure I understand but try this.

Sub hide_rows()


With Worksheets("Caller 1")
.Rows(Range("E41").Value + 8).Hidden = False
.Rows(Range("F41").Value + 8).Hidden = False
.Rows(Range("F41").Value + 9).Hidden = True
End With

End Sub

hth,

Ross

 

[Sanchez88]

Yea sorry, I'm not the best at explaining.

That is kind of what I want, but would it be possible to make it ranges?

For example:

Sub hide_rows()


With Worksheets("Caller 1")

    Rows(Worksheets("Hourly Vote Tally Sheet").Range("E41").Value + 8 : "Hourly Vote Tally Sheet").Range("F41").Value + 8).Hidden = False
    Rows(Worksheets("Hourly Vote Tally Sheet").Range("F41").Value + 9 : 38).Hidden = True

End With

End Sub

Basically the two Cells in "Hourly Vote Tally Sheet", E41 & F41 determine the range of Rows that Reveal in "Caller 1".

For Example

E41 F41
1 - 5

Rows 9 - 13 Unhide

Does that help at all?

 

[rpaulson]

Sub hide_rows()
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Worksheets("Hourly Vote Tally Sheet")
Set ws2 = Worksheets("Caller 1")


ws2.Rows("10:38").Hidden = True 'undie rows as neede

i = ws1.[E41] + 8
j = ws1.[F41] + 8

ws2.Rows(i & ":" & j).Hidden = False
End Sub

 

[Sanchez88]

That works perfectly!!

Thank you so much, this is a life saver.