Have code working but need to edit to use on multiple variable len

[jeremiah]

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,k6: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

 

[John Bundy]

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

 

[jeremiah]

When I leave this as Option Explicit I get the error variable not assigned
for lastcell. If I move it to public then I get an invalid use of property
error when it gets to the range ("a1:A").select. Is there something I need
to define, not good at this part of coding at all.

 

[John Bundy]

With option explicit on, which is almost always the best, you need to tell
the program what kind of variable you are using, try adding this at the top
just after the Sub line
Dim lastCell as Integer
this tells the program that it should expect a whole number, which row
values are

 

[Dave Peterson]

How about something like:

Option Explicit
Sub Macro3A()
Dim wks As Worksheet
Dim LastRow As Long
Dim myRng As Range
Dim myCol As Range

For Each wks In ActiveWorkbook.Worksheets

With wks
'using column A to get the last row
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

Set myRng = Intersect(.Range("b:c,e:f,h:i,k:l"), _
.Rows("4:" & LastRow))
For Each myCol In myRng.Columns
With myCol
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
Next myCol

Set myRng = Intersect(.Range("G:g,J:J"), .Rows("4:" & LastRow))
For Each myCol In myRng.Columns
With myCol.Borders(xlEdgeRight)
.LineStyle = xlDashDot
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Next myCol

'did you really mean to exclude K4:L5 in the first
portion?
Set myRng = .Range("K4:L5")
For Each myCol In myRng.Columns
With myCol.Borders(xlEdgeRight)
.LineStyle = xlNone
End With
Next myCol

End With
Next wks
End Sub

 

[jeremiah]

Actually, no I didn't mean to leave out those rows in K and L. Thanks. I
was initially trying to format all my worksheets at once but realized after I
started that not all of them have the same layout, so I will adjust this to
work on the selected sheets, not all sheets. Thanks again for your help.