Format only occupied cells

Atholl

Active Member
Joined
May 19, 2002
Messages
436
Hi all,

I have a sheet containing data in the column range B5:L??. The data is automatically pasted in by VBA as values only. Trouble is the amount of rows can vary from a one to a maximum of 12,000. I have formatted the entire range B5:L12000 which adds a big chunk to the file size and also causes the workbook to be very slow on opening.
I need to find a way I can format only the cells that are needed and not the full 12,000 rows?
I tried using the Selection.End(xlDown) and Selection.End(xlToRight) commands, but the problem is that the data in column B and the first Row (5) may not necessarily be continuous all the way down and across, although column C would always be continuous to the end row.

Does that make any sense?!

Atholl
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Atholl

Any chance of posting the code that does the copy and paste?
 
Upvote 0
Here you go, the abridged version:

Code:
Tosheet.Range("B5:M12000").Copy
Sheets("AT").Range("B5").Select
Selection.PasteSpecial Paste:=xlValues
 
Upvote 0
Here you go, the abridged version:

Code:
Tosheet.Range("B5:M12000").Copy
Sheets("AT").Range("B5").Select
Selection.PasteSpecial Paste:=xlValues
But what you are saying is that rows 5:12000 may not all be occupied? and that columns B:M may not all be occupied?
 
Upvote 0
Atholl

This is not my strogest point, and is hardly tested, but perhaps something like this added to your code?

<font face=Courier New>   <SPAN style="color:#00007F">With</SPAN> Sheets("AT").Range("B5:M12000").SpecialCells(xlCellTypeBlanks)
    .ClearFormats
   <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN></FONT>
 
Upvote 0
This code works, but unfortunately clears formats empy cells on partially filled rows. I would need some code to detect the last occupied row number (like Selection.End(xlDown)) on column C (which will always be occupied), then copy formats to the range from B5 to M whatever.
 
Upvote 0
Atholl

Not quite sure where you are running this macro from or what your ActiveSheet is but something like this might help:

<font face=Courier New>    <SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    
    Sheets("AT").Activate
    LastRow = Range("C65536").End(xlUp).Row
    Range("B5:M" & LastRow).Select
    
    <SPAN style="color:#007F00">' Now apply your format</SPAN>
    </FONT>
 
Upvote 0

Forum statistics

Threads
1,218,909
Messages
6,145,169
Members
450,592
Latest member
Boulder127

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
Back
Top