Set Range with Long Variable
 

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
--
Cheers,
Ryan

Set Range with Long Variable
 

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
--
jb


"Ryan H" wrote:

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
--
Cheers,
Ryan

Set Range with Long Variable
 

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"))

--
Rick (MVP - Excel)


"john" wrote in message
...
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
--
jb


"Ryan H" wrote:

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
--
Cheers,
Ryan