Apply borders in a used cell Range VBA

Question

I am trying to apply a border around a group of used cells dynamically. The column Range is (B7:E7) The number of rows will always vary, so the code needs to be dynamic. My code below is not achieving this:

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("B7:B" & lngLstRow)
    If rngCell.Value > "" Then
        r = rngCell.row
        c = rngCell.Column
        Range(Cells(r, c), Cells(r, lngLstCol)).Select
            With Selection.Borders
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
    End If
Next

Application.ScreenUpdating = True

End Sub

Solution 1

This code puts borders around all non-empty cells beyond B7.

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

The code below puts borders around the used range beyond B7:

Sub BordersB()

    Application.ScreenUpdating = False

    Dim lngLstCol As Long, lngLstRow As Long

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

    With Range(Range("B7"), Cells(lngLstRow, 2)).Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With

    With Range(Range("B7"), Cells(7, lngLstCol)).Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With

    With Range(Cells(7, lngLstCol), Cells(lngLstRow, lngLstCol)).Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With

    With Range(Cells(lngLstRow, 2), Cells(lngLstRow, lngLstCol)).Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With

    Application.ScreenUpdating = True

End Sub

Solution 2

This will add borders to all none blank cells below row 6 in Columns(B:C)

    Sub AddBorders()
    Dim Rws As Long, Rng As Range, c As Range

    Rws = Range("A1").SpecialCells(xlCellTypeLastCell).Row
    Set Rng = Range(Cells(7, "B"), Cells(Rws, "C"))

    For Each c In Rng.Cells

        If c <> "" Then

            With c.Borders

                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic

            End With

        End If

    Next c

End Sub

Comments

[BrakNicku]

Your code applies border to every row where B column is >"". What do you exactly expect?

[James]

I wanted to be able to apply a boarder around used cells from columns B7:C7 to lastrow so my code is error,

[BrakNicku]

Can you add an image of sample expected output?

[kitap mitap]

This code make borders around all cells B7 and beyond to right and down as expected..

[James]

@user3964075 for example i would like to apply a board around a range such as Range("B6:E12"). The column range does not change it's B6:E6, just the row count varies depending on the data produced. So its basically a Thick Box Border.

[kitap mitap]

Which one do you want? 1) To put a border around all non-empty cells in a range 2) To put a border around a used range. If you have intended second why do you check if the cells are empty?

[James]

@kitapmitap I actually intended for the second one. But the one you created works perfectly as well.

[kitap mitap]

@James I edited my code. You may also use this. This is better, may be faster.

[Davesexcel]

How can this be the correct answer? You asked for borders in Columns(B:C), the code above will put borders on all cells below row 6 and ever column but column A

[James]

@Davesexcel this has been discussed above . apologies for the confusion. Thanks alot.

[kitap mitap]

@James ı added a code which puts around the used range. (Of course it has nothing to do about empty cells.)

[danieltakeshi]

You should avoid .Select, so instead of using Select, just use : With rngCell.Borders or create a new non contiguous Range and then make the action to add the borders.

[kitap mitap]

@danieltakeshi You are right; thanks: it is not needed.