Help required putting borders around cells.
 

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.

Help required putting borders around cells.
 

sheet1.usedrange.select

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

"Centurian" wrote:

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.

Help required putting borders around cells.
 

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





ben wrote:
sheet1.usedrange.select

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

"Centurian" wrote:


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.

Help required putting borders around cells.
 

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


"Centurian" wrote:




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





ben wrote:
sheet1.usedrange.select

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

"Centurian" wrote:


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.

Help required putting borders around cells.
 

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

--
Don Guillett
SalesAid Software

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

Help required putting borders around cells.
 

Don Guillett wrote:
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 )

Help required putting borders around cells.
 

Thanks Everyone.


Regards
Centurian

Help required putting borders around cells.
 

Your welcome.

--
Regards,
Tom Ogilvy


"Centurian" wrote in message
. ..
Thanks Everyone.


Regards
Centurian

Help required putting borders around cells.
 

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

"Tom Ogilvy" wrote:

Your welcome.

--
Regards,
Tom Ogilvy


"Centurian" wrote in message
. ..
Thanks Everyone.


Regards
Centurian

Help required putting borders around cells.
 

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


--
Don Guillett
SalesAid Software

"Centurian" wrote in message
.. .
Don Guillett wrote:
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 )