Formatting a Range
 

I'd like to copy the format/attributes of cell A2
to cells A3- end of sheet. Leaving A1 untouched.

Is this possible ?

Thanks - Kirk

Formatting a Range
 

Select A2
Click the format icon (a paintbrush in the Format toolbar)
Select all target cells in one go
All done

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"kirkm" wrote in message
...
I'd like to copy the format/attributes of cell A2
to cells A3- end of sheet. Leaving A1 untouched.

Is this possible ?

Thanks - Kirk

Formatting a Range
 

Hi Kirk,

Assuming that VBA is required, try something like:

'==========
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim iLastRow As Long

Set WB = Workbooks("MyBook=.xls") '<<==== CHANGE
Set SH = WB.Sheets("Sheet1") '<<==== CHANGE

With SH
iLastRow = LastRow(SH, .Columns("A:A"))
Set Rng = .Range("A3:A" & iLastRow)
.Range("A2").Copy
Rng.PasteSpecial Paste:=xlPasteFormats
End With
Application.CutCopyMode = False

End Sub
'<<=============


---
Regards,
Norman


"kirkm" wrote in message
...
I'd like to copy the format/attributes of cell A2
to cells A3- end of sheet. Leaving A1 untouched.

Is this possible ?

Thanks - Kirk

Formatting a Range
 

Hi Kirk,

To avoid possible confusion,

Set WB = Workbooks("MyBook=.xls") '<<==== CHANGE

contains a typo and was intended as:

Set WB = Workbooks("MyBook.xls") '<<==== CHANGE

However, unless you have a requirement for a
VBA solution, use Bob's suggestion, instead


---
Regards,
Norman

Formatting a Range
 

Thanks very much to both.

I'd try Bobs method but couldn't see how to select
row 2 to 29000 in one go.

Norman, I have an error in line

iLastRow = LastRow(SH, .Columns("A:A"))

Sub or Function not defined referencing "LastRow"

Also, as there's several colums to do this to, can I substitute
another column letter wherever you have "A" ?

Thanks - Kirk

Formatting a Range
 

Hi Kirk

My apologies, I forgot to include the function,

Try:

'==========
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim iLastRow As Long

Set WB = Workbooks("MyBook=.xls") '<<==== CHANGE
Set SH = WB.Sheets("Sheet1") '<<==== CHANGE

With SH
iLastRow = LastRow(SH, .Columns("A:A"))
Set Rng = .Range("A3:A" & iLastRow)
.Range("A2").Copy
Rng.PasteSpecial Paste:=xlPasteFormats
End With
Application.CutCopyMode = False
End Sub

'---------------
Function LastRow(SH As Worksheet, _
Optional Rng As Range)
If Rng Is Nothing Then
Set Rng = SH.Cells
End If

On Error Resume Next
LastRow = Rng.Find(What:="*", _
After:=Rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
'<<=============

To rapidly select rows 2:29000, try typing:

2:2900
in the name box (above cell A1)



---
Regards,
Norman


"kirkm" wrote in message
...
Thanks very much to both.

I'd try Bobs method but couldn't see how to select
row 2 to 29000 in one go.

Norman, I have an error in line

iLastRow = LastRow(SH, .Columns("A:A"))

Sub or Function not defined referencing "LastRow"

Also, as there's several colums to do this to, can I substitute
another column letter wherever you have "A" ?

Thanks - Kirk

Formatting a Range
 

Hi Kirk,

Also, as there's several colums to do this to, can I substitute
another column letter wherever you have "A" ?

Try changing:

iLastRow = LastRow(SH, .Columns("A:D))
Set Rng = .Range("A3:A" & iLastRow)

to (say):

iLastRow = LastRow(SH, .Columns("A:D"))
Set Rng = .Range("A3:D" & iLastRow)



Regards,
Norman

Formatting a Range
 

Just hold-down the mouse key whilst selecting the row headings.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"kirkm" wrote in message
...
Thanks very much to both.

I'd try Bobs method but couldn't see how to select
row 2 to 29000 in one go.

Norman, I have an error in line

iLastRow = LastRow(SH, .Columns("A:A"))

Sub or Function not defined referencing "LastRow"

Also, as there's several colums to do this to, can I substitute
another column letter wherever you have "A" ?

Thanks - Kirk