Adding borders to used range

[Patti]

I am creating a Sub to add borders to an area in my worksheet. The
following will work if I know the range:

Sub Borders()
With Range("a6:f300").Cells.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
End Sub

But I can't use it because range will always vary. I know that my range
will always start in A6 and extend to F6. I don't know how may rows I will
have, but it will end when ever I run out of data in column A. Column F
will be empty except for the header in F6.

Since the following will select the correct area, I tried to use it to set a
range to use in the Sub Borders above. But every variation I have tried has
resulted in an error.

Sub RegionTest()
With [a6].CurrentRegion
.Resize(.Rows.Count).Select
End With
End Sub

What would be the best way to handle this?

TIA, Patti

 

[Bob Phillips]

Hi Patti,

Does this work?

Sub Borders()
With Range("a6:f" & Cells(Rows.Count,"A").End(xlUp).Row).Cells.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Patti]

Like a charm. Thanks my friend.

Patti

Hi Patti,

Does this work?

Sub Borders()
With Range("a6:f" & Cells(Rows.Count,"A").End(xlUp).Row).Cells.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I am creating a Sub to add borders to an area in my worksheet. The
following will work if I know the range:

Sub Borders()
With Range("a6:f300").Cells.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
End Sub

But I can't use it because range will always vary. I know that my range
will always start in A6 and extend to F6. I don't know how may rows I will
have, but it will end when ever I run out of data in column A. Column F
will be empty except for the header in F6.

Since the following will select the correct area, I tried to use it to

set

a
range to use in the Sub Borders above. But every variation I have tried has
resulted in an error.

Sub RegionTest()
With [a6].CurrentRegion
.Resize(.Rows.Count).Select
End With
End Sub

What would be the best way to handle this?

TIA, Patti

 

[gitcypher]

What designates the top-left and bottom-right cells? You can write th
code that Names each of those, and then use the same you had before.

For Example, if you named the top-left 'TLeft' and the bottom-igh
'BRight', you can use

Sub Borders()
With Range("TLeft:BRight").Cells.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
End Sub

-Gitcyphe