Select all rows on a sheet from a fixed starting point
 

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.

Select all rows on a sheet from a fixed starting point
 

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.

Select all rows on a sheet from a fixed starting point
 

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".

Select all rows on a sheet from a fixed starting point
 

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

Select all rows on a sheet from a fixed starting point
 

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".

Select all rows on a sheet from a fixed starting point
 

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