setting column widths in macro

[davegb]

I'm writing a macro to modify 64 files. One of the things I want to
change is some of the column widths. I recorded a macro to change them
by selecting the column headers by control clicking on the various ones
I wanted to set to a particular width. This works fine when I do it
manually, but when I record it, it changes all the column widths
between the ones I selected. So I tried again, this time selecting
adjacent columns, setting the width, then the next set of adjacent
columns, setting the width, etc. But it still changed the column widths
of the columns in between the ones I selected. What is going on? How do
I change only the column widths I want to reset in the macro?
TIA

 

[Harald Staff]

Hi

A macro usually records exactly what you did while recording it. Like this:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 24.01.2005 by Harald Staff
'
Columns("B:C").Select
Selection.ColumnWidth = 5.43
Range("G:G,I:I").Select
Selection.ColumnWidth = 13.43
Range("A1").Select
End Sub

and it does what it promises when you run it. Post your recorded code here
(like above) for suggestions.

HTH. Best wishes Harald

 

[davegb]

"A macro usually records exactly what you did while recording it."
I agree, it usually does. The key word here is "usually". Here is the
code I recorded:

Range("T:W,N:Q,H:K,B:E").Select
Range("E3").Activate
Selection.ColumnWidth = 1.7

(I recorded them in reverse order because when I recorded them in
forward order, the first cell in the group had text in it that covered
the other cell headers and I couldn't see them to select them)
Notice that columns R, S, L, M, F, G, etc. were not selected, but Excel
set their widths to 1.7. Any ideas on why it is doing that? I also
tried selecting one column at a time and setting it's width to 1.7, but
it still set the columns in between to 1.7 as well!

 

[Harald Staff]

Your code runs fine here and does what you want it to do. Test it on a blank
new sheet.

So there's probably something wrong in its environment. Do you have merged
cells in the sheet ?

HTH. Best wishes Harald

 

[davegb]

Harald,
Yes! There are merged cells at the top of the columns whose widths I'm
changing. I didn't realize they would affect this. It still confuses me
why it works when I do it manually, but not in the macro. If anyone
knows why this is so, please let me know. But at least I now know you
can't set column widths from a macro if there are merged cells in those
same columns. I tried inserting a blank row above the merged cells and
doing it that way, but that didn't work either.
Thanks for figuring out, Harald!

 

[Harald Staff]

VBA doesn't handle grouped objects just as good as manual operations. Like
if you select several worksheets and then type into a cell. Just the way it
is I guess. Glad we found the cause, you had me scared there for a little
while.

Best wishes Harald