Formatting a Range

[kirkm]

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

 

[Bob Phillips]

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)

 

[Norman Jones]

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
'<<=============

 

[Norman Jones]

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

 

[kirkm]

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

 

[Norman Jones]

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)

 

[Norman Jones]

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))
Set Rng = .Range("A3:A" & iLastRow)

to (say):

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



Regards,
Norman

 

[Bob Phillips]

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)