Formatting borders for a column until last row of data

[VBAExpertWannabe]

I want to apply borders to the right edge for Column H of my data, starting from J:20 onto the last row of data (there are several rows of empty data in between). I have the following code but somehow, it is not working. Can anybody see what I did wrong? Thanks.

Sub Border()

Range("J" & Rows.Count).End(xlUp).Borders(xlEdgeRight).LineStyle = xlContinuous

End Sub

 

[Michael M]

Try using

Range("J20:J" & Rows.Count).End(xlUp).Borders(xlEdgeRight).LineStyle = xlContinuous

 

[VBAExpertWannabe]

Try using

Range("J20:J" & Rows.Count).End(xlUp).Borders(xlEdgeRight).LineStyle = xlContinuous

Thank you. Hmmmm, its not doing anything.

 

[Michael M]

Try then

Range("J20:J" & (Cells(Rows.Count, "J").End(xlUp).Row)).Borders(xlEdgeRight).LineStyle = xlContinuous

 

[VBAExpertWannabe]

Try then

Range("J20:J" & (Cells(Rows.Count, "J").End(xlUp).Row)).Borders(xlEdgeRight).LineStyle = xlContinuous

That worked PERFECTLY - thank you so much!!!

p.s. how can we learn more about these solutions?? There's no way I would have gotten that answer on my own.

 

[Michael M]

Practice, practice and practice....read the forum and look at solutions provided....then try modifying the solution to try and different results....

 

[VBAExpertWannabe]

Thank you. And just to follow up - I want to apply a single underline to rows, starting from Column C to Column J, there are several empty data in between as well. I don't think I want to specify which rows to underline (for now) but just the rows that I am at. How do I adjust my formula so it goes all the way to the 'last available column with data'?

Range(Selection, Selection.End(xlToRight)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

 

[Michael M]

Try using this goes from C1 to the lastrow in "J"
Also, in future please use code tags when posting code.....Paste the code into the reply window, highlight the code, then press the < vba > button on the toolbar

Sub MM1()
Dim lr As Long
lr = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
With Range("C1:J" & lr).Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
End With
End Sub