Set Range with Long Variable

[Ryan H]

I have 3 ranges I want to add vertical lines too, but I keep getting an error
that says "Wrong Number of Arguements or Invalid Property Assignment."
Anyone know why? Is there a cleaner way to write out this range?

Sub AddLines()

Dim lngFirstRow As Long
Dim lngLastRow As Long

' adds thin vertical lines to separate columns
With Range("B" & lngFirstRow & ":B" & lngLastRow, _
"F" & lngFirstRow & ":F" & lngLastRow, _
"H" & lngFirstRow & ":H" & lngLastRow)
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).Weight = xlThin
End With

End Sub

 

[john]

Ryan,
see if this helps:

Sub AddLines()

Dim lngFirstRow As Long
Dim lngLastRow As Long

Dim MultiRange As Range


Set MultiRange = Union(Range("B" & lngFirstRow & ":B" & lngLastRow), _
Range("F" & lngFirstRow & ":F" &
lngLastRow), _
Range("H" & lngFirstRow & ":H" & lngLastRow))


' adds thin vertical lines to separate columns
With MultiRange

.Borders(xlEdgeLeft).Weight = xlThin

.Borders(xlEdgeRight).Weight = xlThin

End With



End Sub

 

[Rick Rothstein]

Here is shorter way to write that Set statement for the MultiRange
variable...

Set MultiRange = Intersect(Rows(lngFirstRow & ":" & _
lngLastCol), Range("B:B,F:F,H:H"))