Column width issue (1 Viewer)

kilroyscarnival · Dec 26, 2008

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

,G:G,J:J,M:M,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

Bilbo_Baggins_Esq · Dec 27, 2008

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?

Bilbo_Baggins_Esq · Dec 27, 2008

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.

chergh · Dec 28, 2008

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

kilroyscarnival · Dec 29, 2008

Bilbo_Baggins_Esq said:
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

kilroyscarnival · Dec 29, 2008

chergh said:
If it isn't merged columsn causing you a problem you could try:

Nice! It was the merging, but I'll keep this in mind for something else. Thanks!

kilroyscarnival · Dec 30, 2008

Bilbo_Baggins_Esq said:
I'd suggest checking it by putting a break in the code right before the select line.
Then step the code with F8.

With embarrassment I admit I have forgotten how to do this.

Bilbo_Baggins_Esq · Dec 30, 2008

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"

Column width issue (1 Viewer)

kilroyscarnival

Registered User.

Bilbo_Baggins_Esq

Registered User.

Bilbo_Baggins_Esq

Registered User.

chergh

blah

kilroyscarnival

Registered User.

kilroyscarnival

Registered User.

kilroyscarnival

Registered User.

Bilbo_Baggins_Esq

Registered User.

Similar threads

Users who are viewing this thread