How do I remove the bottom cell border line from entire row when clicking a shape?-CodePudding

I have shape that I use as a button. When i click this button I can hide/unhide a range of rows bellow the button. When I click this button I also want to have the bottom border line removed from the entire row where the button is placed.

This is my code.

    Sub ArrowClick()

    Dim r As Integer
    Dim r1 As Integer
    Dim r2 As Integer
    Dim sr1 As String
    Dim sr2 As String

    r = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row

    r1 = r   1
    r2 = r   9

    sr1 = CStr(r1)
    sr2 = CStr(r2)


    If ActiveSheet.Rows(sr1 & ":" & sr2).EntireRow.Hidden = True Then
        ActiveSheet.Rows(sr1 & ":" & sr2).Hidden = False
     
    ElseIf ActiveSheet.Rows(sr1 & ":" & sr2).Hidden = False Then
        ActiveSheet.Rows(sr1 & ":" & sr2).Hidden = True

    End If


    End Sub

I'm a beginner, so feel free to point out any improvments of my code. Any help would be appreciated.

CodePudding user response：

Something like this:

Sub ArrowClick()
    With ActiveSheet.Shapes(Application.Caller).TopLeftCell.EntireRow
        .Borders(xlEdgeBottom).LineStyle = xlNone
        With .Offset(1, 0).Resize(9)
            .Hidden = Not .Hidden
        End With
    End With
End Sub

Getting comfortable using Offset() and Resize() will help you a lot when writing VBA in Excel.