you can get the last used cell in a particular column with
lastcell = ActiveSheet.Cells(Rows.Count, "i").End(xlUp).Row
change the i to the column you want the link of, then change your formulas
from h2:h42 to "h2:h" & lastcell

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"jeremiah" wrote:

Have code working but need to edit to use on multiple variable length
worksheets instead of just applying the lines to a selected range. I have
probably 100 sheets that all need the same formatting but the sheets do not
all contain the same number of records. This will work for the selected
sheets but if I don't give the ending cell I get an error.

Sub Macro3()
range("b4:b42,c4:c42,e4:e42,f4:f42,h4:h42,i4:i42,k 6:k42,l6:l42").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
range("G4:G45, J4:J45").Select
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDashDot
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
End Sub