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

margram

New Member
Joined
Jul 6, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 6, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Thanks for all your help GWteB, I think I've got my tables pretty well buttoned up... for now
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
You are welcome and thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,606
Messages
5,625,767
Members
416,136
Latest member
senthil_sk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top