Only change formats up to the "last cell"

[smkyle]

I have a macro that selects all cells in all worksheets and changes the text to 'times new roman'
I suspect this is causing excel to consume more resources than it would otherwise.

Here is my super sophisticated macro:

Please Login or Register  to view this content.

I prefer to have a buffer of maybe 50 columns and 50 rows. The effect on resources would be negligible? Any help is appreciated. Thanks!

[AB33]

Please Login or Register  to view this content.

It is not looping through the worksheets.
You are selecting all cells and try to apply formatting for all cells which is memory hungry.
Which columns in each sheet do you want to apply the formatting for?

[smkyle]

No, it is not looping. For my purposes selecting all of the visible sheets and applying the formatting executes quickly enough. However, I suspect the improved macro will have to loop.

I want to apply the formatting to each row and column with non-empty cells. I also want to apply it to some more on the side and bottom. I was thinking this might be done more efficiently if the formatting was applied relative to the "last cell" (the cell selected when pressing CTRL + END)

It may be the case that the problem isn't with my macro, but with the spreadsheet I inherit and execute the macro on. I notice that when I run it on some spreadsheets it behaves the way I want it to.

[AB33]

May be

Please Login or Register  to view this content.

[smkyle]

Let me explain the specific problem that I am having. I have a large spreadsheet. When I press CTRL + END it sends me to column XFD (the most columns supported I believe). I only have about 50 columns. I got the impression from another thread that this could be caused by formatting more cells than necessary. I want to ensure that my macro isn't the culprit. Therefore, I want to limit the cell formatting to a more reasonable range. My macro as is successfully changes all fonts in all sheets to times new roman which was my original goal.

[AB33]

smkyle,
Please do not reply with Quote. Just a reply will do.
I was trying to make a point that selecting the entire cells will consume speed. If you select all cells and try to apply formatting consumes lots of memory, hence I suggested you should restrict your range.

[smkyle]

I removed the quotes from my earlier posts.

My apologies for being ignorant of this difference, but are you saying that removing .select will speed things up? How do I automate the process of restricting my range? Is this something that Excel knows to do?

[AB33]

Instead of cells, you can use

Please Login or Register  to view this content.