CODEWITHSUNDEEP
excelvba
joey
joey

Reputation: 31

How to use the OFFSET function in Rows() function in vba

I want to write a function that will hide certain rows based on what drop down option you've selected. I have to reuse this code multiple times in the worksheet so I dont want it to be cell dependent, and instead use the OFFSET function.

This is what I have written

Tcell = Range("D3")

If Tcell = "example 1" Then 
    Rows("4:19").Hidden = False
    Rows("12:19").Hidden = True
ElseIf Tcell = "example 2" Then
    Rows("4:19").Hidden = False
    Rows("10:19").Hidden = True
ElseIf Tcell = "example 3" Then
    Rows("4:19").Hidden = False
    Rows("16:19").Hidden = True
End If

I tried using something like this below, but it had an type 13 match error. Thank you for your help!

 If Tcell = "example 1" Then 
    Rows("Tcell.Offset(1):Tcell.Offset(16)").Hidden = False
    Rows("Tcell.Offset(1):Tcell.Offset(16)").Hidden = True

Upvotes: 3

Views: 837

Answers (1)

BigBen
BigBen

Reputation: 50008

Offset is a property of a Range object and does not belong within parentheses. Neither does Tcell.

First change Tcell to be a Range and Set it.

Then you can use Offset and Resize here:

Dim Tcell As Range
Set Tcell = ActiveSheet.Range("D3")

If Tcell.Value = "example 1" then
    ActiveSheet.Rows(Tcell.Row).Offset(1).Resize(16).Hidden = False 
    ActiveSheet.Rows(Tcell.Row).Offset(9).Resize(8).Hidden = True

' and similarly for the other cases

End If

Upvotes: 3

Related Questions