Add borders in a used cell range (including empty/blank cells) in VBA-CodePudding

Ok so I am trying to apply borders in VBA in the column range of A:BQ, the number of rows will never be the same so this part needs to be dynamic. I have tried some versions but these versions are excluding empty cells with will be within the range. This is the code I am working with and like I mentioned, it excludes any blank cells and doesn't give those a border.

Sub Borders()

Application.ScreenUpdating = False

Dim lngLstCol As Long, lngLstRow As Long

lngLstRow = ActiveSheet.UsedRange.Rows.Count
lngLstCol = ActiveSheet.UsedRange.Columns.Count

For Each rngCell In Range(Range("B7"), Cells(lngLstRow, lngLstCol))
    If rngCell.Value > "" Then
        rngCell.Select 'Select cells
        With Selection.Borders
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
    End If
Next

Application.ScreenUpdating = True 

End Sub

CodePudding user response：

You have a condition in the code to exclude the empty cells on this row:

If rngCell.Value > "" Then

Simply comment this row (and its corresponding End If) and you should be all set I think.

CodePudding user response：

No need to loop if you just want to add borders to the whole range

Dim lastCell As Range
With ActiveSheet
    Set lastCell = .UsedRange.Cells(.UsedRange.Cells.Count)
    With .Range("B7", lastCell).Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
End With