Format only occupied cells

Atholl

Active Member
Joined
May 19, 2002
Messages
434
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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,661
Office Version
  1. 365
Platform
  1. Windows
Atholl

Any chance of posting the code that does the copy and paste?
 

Atholl

Active Member
Joined
May 19, 2002
Messages
434
Here you go, the abridged version:

Code:
Tosheet.Range("B5:M12000").Copy
Sheets("AT").Range("B5").Select
Selection.PasteSpecial Paste:=xlValues
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,661
Office Version
  1. 365
Platform
  1. Windows
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?
 

Atholl

Active Member
Joined
May 19, 2002
Messages
434

ADVERTISEMENT

Hi Peter,


Yes, that's right.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,661
Office Version
  1. 365
Platform
  1. Windows
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>
 

Atholl

Active Member
Joined
May 19, 2002
Messages
434

ADVERTISEMENT

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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,661
Office Version
  1. 365
Platform
  1. Windows
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>
 

Forum statistics

Threads
1,141,721
Messages
5,708,092
Members
421,546
Latest member
delatollas

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