Hello,

I'm trying to throw a macro together that will do some recurring formatting
for me.
I need to select a specific row to grab its formatting, then select a
starting row (fixed) and an end row (flexible) and apply the formatting to
that selection.
Afterwards, I want to select another row (fixed) and take its formatting,
and apply that to all the rows in the previous range that fulfill a certain
condition, in this case, contain a certain letter.

I started with the macro recorder for the first part of this task, and it
came up with the following (I removed all the scroll commands):

Rows("4:4").Select
Application.CutCopyMode = False
Selection.Copy
Rows("5:5").Select
Rows("5:190").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

So instead of Rows("5:190").Select, I'd like
Rows("5:last.row.with.a.value").Select.

How can that be done?

Thank you.

Try this

lastrow = Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row
Rows("5:" & lastrow).Select

Mike

"Niniel" wrote:

Hello,

I'm trying to throw a macro together that will do some recurring formatting
for me.
I need to select a specific row to grab its formatting, then select a
starting row (fixed) and an end row (flexible) and apply the formatting to
that selection.
Afterwards, I want to select another row (fixed) and take its formatting,
and apply that to all the rows in the previous range that fulfill a certain
condition, in this case, contain a certain letter.

I started with the macro recorder for the first part of this task, and it
came up with the following (I removed all the scroll commands):

Rows("4:4").Select
Application.CutCopyMode = False
Selection.Copy
Rows("5:5").Select
Rows("5:190").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

So instead of Rows("5:190").Select, I'd like
Rows("5:last.row.with.a.value").Select.

How can that be done?

Thank you.

That worked very nicely, thank you very much.
If it's not too much to ask for, I'd also like to tackle the second part of
my problem - I need to select the same rows again, and this time apply the
formatting of row 3 to all rows where the cell in column A contains a "P".

Or maybe it would be better to not select everything the second time, but
only rows with the letter "P" in column 1.

Maybe this

Sub Macro2()
Rows("3:3").Copy
lastrow = Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row
Rows("5:" & lastrow).Select
For x = 5 To lastrow
Cells(x, 1).Select
If ActiveCell.Value = "P" Then
ActiveCell.PasteSpecial Paste:=xlPasteFormats
End If
Next
End Sub

Mike

"Niniel" wrote:

That worked very nicely, thank you very much.
If it's not too much to ask for, I'd also like to tackle the second part of
my problem - I need to select the same rows again, and this time apply the
formatting of row 3 to all rows where the cell in column A contains a "P".

Yes, that's it. Thank you very much!