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

Question

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

BigBen · Accepted Answer · 2021-05-19 16:16:47Z

3

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

edited May 19, 2021 at 16:16

answered May 19, 2021 at 15:56

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Amiga500 Over a year ago

You'll need to Dim Tcell as a range I think and then set it using "set Tcell = Range("D3")"

BigBen Over a year ago

@Amiga500 - good point. I overlooked the Tcell = Range("D3") since it was outside the code block in the question. Edited to fix that and the answer.

joey Over a year ago

@BigBen Thank you for your help. I was wondering where the 9 and 8 come from in the second line ? Sorry, I just started using vba a couple weeks ago.

BigBen Over a year ago

That's just simple math. If your starting cell is in row 3 and you want to get to row 12, you offset 9 rows. Then Rows("12:19") contains 8 rows.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related