Help required putting borders around cells.

[Centurian]

Can anyone help ?

I need to put border on a number of cells
of which number of rows is unknown.

The first routine below was originally used to find
the number of rows and then fill down the columns.

The 2nd part is a recording of keypresses to ut the
border on the cells.

HOW do I marry the two parts together in order to
put borders inside and around Range ("A3:F" & LastRows)



Dim LastRow As Long
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("B3").AutoFill Destination:=.Range("B3:B" & LastRow) _
, Type:=xlFillDefault

----------------------------------------------------------------


Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With



Regards
Centurian.

 

[Guest]

sheet1.usedrange.select

this will select every single used cell
then just add your border code to the bottom of that

 

[Centurian]

Ben,

I am having problems.

Ran the macro with the suggested alteration
and it halted at:

With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic


I got a Runtime 1004 Error
When debug was pressed
..LineStyle = xlContinuos
was hightlighted in yellow.


For your info, I am running version Excel 97
and the macro is called Sub Macro1()

 

[Guest]

try deleting the with statements of the INSIDEHORIZONTAL and INSIDEVERITICAL
borders that may still get you the results you want
delete
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

 

[Don Guillett]

try this - UN tested

Dim LastRow As Long
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row

.Range("B3").AutoFill Destination:=.Range("B3:B" & LastRow) _
, Type:=xlFillDefault
.range("b3:f" & lastrow).Borders.LineStyle = xlContinuous
end with
End Sub

 

[Tom Ogilvy]

Ben,
Just for information,
INSIDEHORIZONTAL and INSIDEVERITICAL [sic]

are only valid when you are addressing a contiguous block of cells (rather
than just one).

Same reason the code you posted previously in another thread raises an
error.

 

[Tom Ogilvy]

Try this:

Sub ABCD()
Dim LastRow As Long
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("B3").BorderAround Weight:=xlThin
.Range("B3").AutoFill Destination:=.Range("B3:B" & LastRow) _
, Type:=xlFillDefault
End With
End Sub

 

[Tom Ogilvy]

Ben,
That was stated poorly and comes across as derogatory - my apologies - there
was no intent to disparage your contributions which are excellent. The
intent was to share some information. It should have said

This is the reason the code you posted previously in another thread raises
an
error.

In this current thread, you *have* recommended selecting a block of cells
and the code combined with your suggestion should not be a problem. I
suspect the OP did not implement it correctly and the Inside settings are
not causing the problem.

--
Regards,
Tom Ogilvy

Ben,
Just for information,
INSIDEHORIZONTAL and INSIDEVERITICAL [sic]

are only valid when you are addressing a contiguous block of cells (rather
than just one).

Same reason the code you posted previously in another thread raises an
error.

--
Regards,
Tom Ogilvy

try deleting the with statements of the INSIDEHORIZONTAL and INSIDEVERITICAL
borders that may still get you the results you want
delete
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

LastRow)

_

 

[Centurian]

try this - UN tested

Dim LastRow As Long
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row

.Range("B3").AutoFill Destination:=.Range("B3:B" & LastRow) _
, Type:=xlFillDefault
.range("b3:f" & lastrow).Borders.LineStyle = xlContinuous
end with
End Sub

Thanks Don,

This worked beautifully after adjusting the range.

..Range("A3").AutoFill Destination:=.Range("A3:F" & LastRow) _
, Type:=xlFillDefault
..range("A3:F" & lastrow).Borders.LineStyle = xlContinuous

May I be a cheeky so and so and ask, how do I get the
macro to set PrintArea to the new range each time
the macro is run.

..range("A3:F" & lastrow) ??????????


I've looked in the object browser but cant see anything
obvious to use.

Regards
Centurian

aka... Kevin ( Lancashire, England, Europe )

 

[Centurian]

Thanks Everyone.


Regards
Centurian

 

[Tom Ogilvy]

Your welcome.

 

[Guest]

no worries Tom, i finally realized why that code causes an error, believe me
i always value your input.
Cheers

 

[Don Guillett]

Glad to help.


May I be a cheeky so and so and ask, how do I get the

macro to set PrintArea to the new range each time
the macro is run.

try
.range("A3:F" & lastrow).address