Code required to run a VBA Macro that runs on all cells in a sheet with values (not on ones without)

[margram]

Hello,

I have a macro that copies and pastes conditional formatting into a range of cells on a sheet, then runs some more code on these cells. My issue is that the range is predefined (T11:FZ45) and is subject to change by the user. The predefined range was chosen as a likely maximum amount of cells that would ever have values. However, running my code on all these cells takes about 6-10 seconds and I want to cut this down as there is usually a lot less values then the predefined amount. Therefore, I want this range to be dynamic based on the the last cell that has a value (T11:Last Cell With Value). Is this possible? I'd also like to embed this in my current macro. See below.

Sub Button7_Click()
'~~> Defines last cell for ranges below

'~~> Runs conditional formatting on select cells
Sheets("Sheet1").[A11:A45].Copy: Sheets("Sheet1").[T11:FZ45].PasteSpecial xlPasteFormats
'~~> Places formatting into cell permanently and deletes conditional formatting
Dim mySel As Range, aCell As Range
Set mySel = Sheets("Sheet1").[T11:FZ45]
For Each aCell In mySel
With aCell
.Font.FontStyle = .DisplayFormat.Font.FontStyle
.Interior.Color = .DisplayFormat.Interior.Color
.Font.Strikethrough = .DisplayFormat.Font.Strikethrough
End With
Next aCell

mySel.FormatConditions.Delete

End Sub

Thank you,

 

[margram]

Thanks for all your help GWteB, I think I've got my tables pretty well buttoned up... for now

 

[GWteB]

You are welcome and thanks for the feedback.