CODEWITHSUNDEEP
vbaexcelloops
Max
Max

Reputation: 31

VBA How to create Cell Bottom Border every 5 rows for only Visible Cells

I am looking to apply a bottom border to every 5th visible row.

The loop starts with row 14 and continues through row 200.

I would like for the loop to look for a value in cells(i, "D"). ie every ith row in column D.

Currently I am getting an Object Required error on the row where I set x = 0. I am confused about this since I declared x as an Integer at the top.

Sub Border()

Dim i As Integer
Dim x As Integer
Dim sumsht As Worksheet
Set sumsht = ThisWorkbook.Sheets("Sheet1")

x = 0

  For i = 14 To 200
    x = sumsht.Cells(i, "D") + x
        If x = 5 Then
            With Worksheets("Sheet1").Rows(i).Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = 1
            End With
        Else
        End If  
  Next i

End Sub

Upvotes: 1

Views: 2774

Answers (1)

atclaus
atclaus

Reputation: 1176

Try this. You need to account for the height of the row.

Also as others said, set is used with ranges not just variables (like your x). You also use sumsht but do not define it anywhere.

Option Explicit
Sub add_lines()
Dim rw, ct As Integer


ct = 0

For rw = 14 To 200
    If Rows(rw).Height <> 0 Then
        ct = ct + 1
    End If
    If ct = 5 Then
        With Worksheets("Sheet1").Rows(rw).Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = 1
            End With
        ct = 0
    End If
Next rw

End Sub

to remove other borders use this (thanks @Comintern):

Range("A1:D22").Borders.LineStyle = xlLineStyleNone

Upvotes: 4

Related Questions