CODEWITHSUNDEEP
vbaexcel
Mark Romano
Mark Romano

Reputation: 711

VBA - Hide/unhide row based on hidden/unhidden status of another row

I'm trying to write some VBA code that will unhide an entire row if another specific row is hidden. This macro also hides a range of rows based on the value in a specific column. This aspect works fine - I have reliable code. I can't get the first function I described to work. Should be easy to do, just don't know the syntax. This subroutine should execute upon opening the workbook.

Private Sub Workbook_Open()
Application.ScreenUpdating = False

Dim targ As Range
Dim msg As Range

targ = "DETAILS!B6"
msg = "DETAILS!B42"

msg.EntireRow.Hidden = True


With Range("DETAILS!B6:B40")
        .EntireRow.Hidden = False

        For Each cell In Range("DETAILS!B6:B40")
        Select Case cell.Value

        Case Is = 0
        cell.EntireRow.Hidden = True

        End Select
        Next cell
End With


If targ.EntireRow.Hidden = True Then
    msg.EntireRow.Hidden = False
End If

Application.ScreenUpdating = True
End Sub

Upvotes: 1

Views: 3033

Answers (3)

BruceWayne
BruceWayne

Reputation: 23283

Oh! Just put Set before targ and msg since they're a Range. When declaring ranges, you have to have Set, i.e. Set myRng = Range("A1:A10").

You might need to do Set targ = Range("Details!B6") if just Set Targ = "DetailsB6" doesn't work.

On second thought, I don't think Set Targ = "Details!B6" will work if you are Dim Targ as Range. You're dim'ing as a Range, but are declaring it as like a string. You need this to be a Range, to use it like targ.EntireRow.Hidden, etc.

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152450

Though you can call range objects like this : Range("DETAILS!B6:B40")

In vba it is better accepted to call it like this: Sheets("DETAILS").Range("B6:B40")

I fixed a few more syntax errors:

Private Sub Workbook_Open()
Application.ScreenUpdating = False

Dim targ As Range
Dim msg As Range

Set targ = Sheets("DETAILS").Range("B6")
Set msg = Sheets("DETAILS").Range("B42")

msg.EntireRow.Hidden = True


With Sheets("DETAILS").Range("B6:B40")
        .EntireRow.Hidden = False
End With

For Each cell In Sheets("DETAILS").Range("B6:B40")
    Select Case cell.Value
        Case 0
            cell.EntireRow.Hidden = True
    End Select
Next cell



If targ.EntireRow.Hidden = True Then
    msg.EntireRow.Hidden = False
End If

Application.ScreenUpdating = True
End Sub

Upvotes: 1

Wyatt Shipman
Wyatt Shipman

Reputation: 1789

You need to set the variables like below

Private Sub Workbook_Open()
Application.ScreenUpdating = False

Dim targ As Range
Dim msg As Range

Set targ = "DETAILS!B6"
Set msg = "DETAILS!B42"

msg.EntireRow.Hidden = True


With Range("DETAILS!B6:B40")
    .EntireRow.Hidden = False

    For Each cell In Range("DETAILS!B6:B40")
    Select Case cell.Value

    Case Is = 0
    cell.EntireRow.Hidden = True

    End Select
    Next cell
End With


If targ.EntireRow.Hidden = True Then
    msg.EntireRow.Hidden = False
End If

Application.ScreenUpdating = True
End Sub

Upvotes: 2

Related Questions