Hiding rows based on button location in VBA

Question

Okay so I'm trying to create a Show Hide button in excel, this is a big document so I don't want to create a new sub for each individual button with a new range for each one, I ideally want it to go off the button's location and I want to show/hide the 10 rows below the button. I've found a sub that joins both my 'Show' sub and my 'Hide' sub so I only have to use one button for this, but here is what I have so far:

Sub Hide()

Dim addr As Object, rs As Long, cs As Long
'addr is the address of the button
'rs is the row number
'cs is the column number
Dim offset1 As Long
'offset for rows value
Dim offset2 As Long
'offset 2 for row value
Dim rs1 As Long
Dim rs2 As Long
Dim rng As Range
Dim sheet As Worksheet
'rng is the cell the button is in

Set sheet = Worksheets("Sheet1")

Set addr = sheet.Shapes(Application.Caller)
'address of the shape using the macro (i.e. the button)
With addr.TopLeftCell
'coordinates of the top left cell of the button
rs = .Row
'row number
cs = .Column
'column number
End With

offset1 = -1
offset2 = -10

rs1 = rs + offset1
rs2 = rs + offset2


With sheet
.Cells(rs1 & ", " & cs).Select
Set rng = .Range(.Cells(rs1 & "," & cs), .Cells(rs2 & "," & cs))

End With
' let rng be the cell the button is in

rng.EntireRow.Hidden = True

End Sub

I had this working for my first button using the .offset and .resize functions but when I tried it in another location it didn't work without changing the offsets completely.

Thanks in advance

And it has to be a button? Is your plan to have several buttons or one button you move around? — Andreas, Jun 01 '18 at 08:14
You seem to have enough answers to get what you want. I just want to add that instead of using `rng.EntireRow.Hidden = True` which will only hide the row use `rng.EntireRow.Hidden = Not rng.EntireRow.Hidden` which will hide it if it's not and unhide it if it is. — Darren Bartrup-Cook, Jun 01 '18 at 08:34

score 0 · Answer 1 · answered Jun 01 '18 at 08:16

0

The problem is in your use of Cells. If you give it two integers, they are two separate parameters and not concatenated. Change

.Cells(rs1 & ", " & cs).Select
Set rng = .Range(.Cells(rs1 & "," & cs), .Cells(rs2 & "," & cs))

to

.Cells(rs1, cs).Select
Set rng = .Range(.Cells(rs1, cs), .Cells(rs2, cs))

answered Jun 01 '18 at 08:16

Sam

5,424
1
18
33

Your solution along with Vityata's worked great thank you! Think I need to work on my syntax! – GMoss Jun 01 '18 at 09:24

score 0 · Answer 2 · answered Jun 01 '18 at 08:26

The Cells() takes 2 (or 1) Long arguments as parameters. You are giving this instead: Cells(rs1 & "," & cs), which is not exactly a correct parameter.

Try to do the following:

With Sheet
    .Cells(rs1, cs).Select
    Set Rng = .Range(.Cells(rs1 & "," & cs), .Cells(rs2 & "," & cs))
End With

If you read How to avoid using Select in Excel VBA, you may change your code to this:

With Sheet
    .Range(.Cells(rs1, cs), .Cells(rs2, cs)).EntireRow.Hidden = True
End With

Thus, avoiding Select and writing less code.

I was trying to minimize my use of select but I couldn't work out how, this worked perfectly thank you! — GMoss, Jun 01 '18 at 09:24

score -1 · Answer 3 · answered Jun 01 '18 at 08:28

It seems to me you are overcomplicating things.

There are two main issues.

Positioning of button is relative (that means it's not bound to any specific row or column). You can however estimate yourself on which row your button ends.

For example

We can see the button ends roughly on row 8. So we can hide rows 10 to 20 (or whatever you wish).

As to the implementation, it seems unnecessarily complicated. If you want to simply hide 10 rows below the button, then you can do it easily.

With the following code, which will make it toggle-able:

Private Sub hide_button_Click()
   If (Rows("10:20").EntireRow.Hidden = True) Then
       Rows("10:20").EntireRow.Hidden = False
   Else
       Rows("10:20").EntireRow.Hidden = True
   End If
End Sub

EDIT: In your question you said yo wanted to use 10 rows so i made it into a static value, obviously if you are working with dynamic range, change the interval 10:20 to whatever variable you have storing the starting and ending range of your data

I had something similar initially but I don't wish to keep changing the sub for each button I have, all the buttons will be in the same column and there will be 40 in total and I don't want to write out the range for each individual one. — GMoss, Jun 01 '18 at 09:20
@GMoss, that's why it's in the procedure. Just call the procedure. If you are working with a dynamic range, then read my edit — Samuel Hulla, Jun 01 '18 at 09:26

Hiding rows based on button location in VBA

3 Answers3