Column width issue

kilroyscarnival

Registered User.
Local time
Today, 23:25
Joined
Mar 6, 2002
Messages
76
Happy holidays, everyone!

I've written a simple macro to do some autoformatting of an Excel spreadsheet. When I reduce to 100% zoom, I want to widen every third column so the contents display fully.

Range("D:D,G:G,J:J,M:M,P:P,S:S,V:V,Y:Y").Select
Selection.ColumnWidth = 2.1

When I run the macro, EVERY column becomes 2.1 in width. Am I missing something obvious?

I have even tried isolating these two lines to make sure the error isn't elsewhere. Why would this command affect columns C, E, F, etc? I don't get it. Is there a reliable workaround?

Best regards,

Ann
 
Last edited:
That is the proper code.

I recorded a macro, and except for an "activate" line, matched yours exactly.
I even pasted your code into a blank VBA on my Excel 2007 and it runs fine.

I'd suggest checking it by putting a break in the code right before the select line.
Then step the code with F8.
Size the windows first so you can see what is happening and also the actual state of the sheet just prior to the format being applied.

Despite what your code shows, it would seem that more is selected at the time the format is applied.

Are even columns A:C being formatted or is it just columns D:Y?
 
After testing the above, and as a potential work around (if still needed after the above), you could try to limit your selection/format cycle to one column at a time.


Wait a minute, I just remembered a catch and actually just reproduced what may be happening for you.

Do you have any horizontally merged cells in these rows?
If so, a format applied to one of the columns will also apply to the merged columns.
 
If it isn't merged columsn causing you a problem you could try:

Code:
for i = 4 to 25 step 3
   worksheets("SheetName").Columns(i).width = 2.1
next i
 
After testing the above, and as a potential work around (if still needed after the above), you could try to limit your selection/format cycle to one column at a time.


Wait a minute, I just remembered a catch and actually just reproduced what may be happening for you.

Do you have any horizontally merged cells in these rows?
If so, a format applied to one of the columns will also apply to the merged columns.


That is exactly IT, thanks. I had received an Email of your earlier post, but was training an intern today and hadn't gotten back to it, but it occurred to me that it MUST be related to the merged column headers across three columns. It wasn't happening to anything to the left of D, so that tipped me off, finally.

Thanks for your help! I guess I'll just add a step unmerging the headers then merging them afterwards. It's further complicated by the fact that the reports are generated by a third-party system (Nielsen tv ratings) which comes with its own formatting challenges, but I'm getting closer to what I want.

Best regards,

Ann
 
I just use a message box, and then instead of hitting OK, press CTRL+BREAK

There may be fancier ways of doing it, but a message box works for me.

MsgBox "break"
 

Users who are viewing this thread

Back
Top Bottom