Runtime error 1004 when putting borders around cells

Question

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,

To make your first sub work, change `.UsedRange _` (including the **underscore**) into `.CurrentRegion`. `UsedRange` is a worksheet property, not a range one. To start solving the error in your second sub, have a look at: https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba — ouroboros1, May 04 '22 at 11:53
I think you are not using properly the `With` statemnt. Google for it because it's not needed in your first code. Also, please, specify what are you trying to achieve. Something like `ThisWorkbook.Worksheets("Sheet1").Range("J16").Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous` should work for you — Foxfire And Burns And Burns, May 04 '22 at 11:56
`.Range("J16" & .Rows.Count)` it's a strange construction leading to a row bigger than all existing in that sheet. It should be `.Range("J" & .Rows.Count).End(xlUp).Row`, but you cannot set borders to a sheet. You can do it for a range... — FaneDuru, May 04 '22 at 12:02

score 1 · Accepted Answer · answered May 04 '22 at 12:04

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

score 0 · Answer 2 · answered May 04 '22 at 12:13

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

Runtime error 1004 when putting borders around cells

2 Answers2

Reference a Column Range Using UsedRange