Runtime error 1004 when putting borders around cells-CodePudding

I'm trying to put borders around a column that starts from a certain cell but I'm getting different error codes for each issue.

Original Code

Sub Borders()

With ThisWorkbook.Worksheets("Sheet1").Range("J16").UsedRange _
    .Borders(xlEdgeBottom) _
        .LineStyle = XlLineStyle.xlContinuous
        
End With

End Sub

The code above comes up with a runtime error 438 because the object or the method I have used is incorrect so I tried to rectify it by using the code below.

New Code

Sub Borders()

With ThisWorkbook.Worksheets("Sheet1")
    LastRow = .Range("J16" & .Rows.Count).End(xlUp).Row _
    .Borders(xlEdgeBottom) _
        .LineStyle = XlLineStyle.xlContinuous
        
End With

End Sub

The second code came up with a 1004 execution error meaning that I've named the range incorrecty, but I'm not sure how.

I was wondering what I could do to fix the issue?

Thanks,

CodePudding user response：

The With Statement is just to avoid typing several times the same reference/object.

With ThisWorkbook.Worksheets("Sheet1")
    LastRow = .Range("J" & .Rows.Count).End(xlUp).Row
    .Range("J16:J" & LastRow).Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous
End With

Without With the code would look like this:

LastRow = ThisWorkbook.Worksheets("Sheet1").Range("J" & ThisWorkbook.Worksheets("Sheet1").Rows.Count).End(xlUp).Row
ThisWorkbook.Worksheets("Sheet1").Range("J16:J" & LastRow).Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous

Both codes do exactly the same, but first one it's easier to read and type

With statement

CodePudding user response：

Reference a Column Range Using UsedRange

Sub ReferenceColumn()
    
    Dim crg As Range
    
    With ThisWorkbook.Worksheets("Sheet1").Range("J16")
        ' '.Resize(ws.Rows.Count - .Row   1)' means 'J16:J1048576'
        Set crg = Intersect(.Resize(ws.Rows.Count - .Row   1), ws.UsedRange)
        If crg Is Nothing Then Exit Sub
    End With
    
    crg.Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous

End Sub