Copy format to range but only as far as last row with data

[Calinora]

Hello,

I hope someone can help me with my question.

I have a formula that resets an area to the formatting of a specific line:

Sub Resetformatting ()
    Range("B17:W17").Select
    Selection.Copy
    Range("B18:W468").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

End sub

This works fine but I would like to add code that determines the area to be from B18 and end at the last row containing data. instead of B18:W468.

Thanks,

 

[Fluff]

This will copy to the format to the last used row in col W

Sub Resetformatting()
    Range("B17:W17").Copy
    Range("B18", Range("W" & Rows.Count).End(xlUp)).PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False

End Sub

 

[Rick Rothstein]

Hello,

I hope someone can help me with my question.

I have a formula that resets an area to the formatting of a specific line:

Sub Resetformatting ()
    Range("B17:W17").Select
    Selection.Copy
    Range("B18:W468").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

End sub

This works fine but I would like to add code that determines the area to be from B18 and end at the last row containing data. instead of B18:W468.

You can calculate the last row number for the cell containing the last data value no matter what column that last data cell is in using this code line...

LastDataRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious, , , False).Row

You can then concatenate LastDataRow in place of the 468 value.