Avoid using Select and Selection

[rex759]

Hello,
The code below does what I want, it adds boarders to ranges in sections that contain text. If a row is blank, is doesn’t add the boarders which is exactly what I want it to do. However, I don’t want to be on the active sheet and trying to work around the Select and Selection commands.

I cant seem to get past this line:

WS.Range(Cells(R, c), Cells(R, lngLstCol)).Select

Full Code

Sub addboarders()
Application.ScreenUpdating = False

Dim lngLstCol As Long, lngLstRow As Long
Dim WS As Worksheet

Set WS = Sheet4

lngLstRow = WS.UsedRange.Rows.Count

lngLstCol = WS.UsedRange.Columns.Count



For Each rngCell In WS.Range("A2:A" & lngLstRow)

If rngCell.Value > "" Then
R = rngCell.Row
c = rngCell.Column

WS.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

Any help is appreciated

 

[Fluff]

How about

With ws.Range(ws.Cells(r, c), ws.Cells(r, lngLstCol)).Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

 

[rex759]

Hello and thank you for that quick response.

I replaced just this section:

WS.Range(Cells(R, c), Cells(R, lngLstCol)).Select
With Selection.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic

End With

with your code and I didnt get an error message but it did not apply the boarders.

For Each rngCell In WS.Range("A2:A" & lngLstRow)
    If rngCell.Value > "" Then
        R = rngCell.Row
        c = rngCell.Column
        
        With WS.Range(WS.Cells(R, c), WS.Cells(R, lngLstCol)).Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
        End With
        
        'WS.Range(Cells(R, c), Cells(R, lngLstCol)).Select
        '    With Selection.Borders
        '        .LineStyle = xlContinuous
        '        .Weight = xlThin
        '        .ColorIndex = xlAutomatic
        '    End With
    End If
Next

 

[Fluff]

That code works for me. Can you post your entire code.

 

[rex759]

I apologized sir, it works perfectly. I have many test sheets in this workbook and was looking at a different tab when I ran the code.
In retrospect, I was close but missed the second set of WS.Cells.

Thank you very much.

 

[Fluff]

Glad it's sorted & thanks for the feedback.