Microsoft Office Forums - View Single Post - Adjust Two Columns Without Changing Combined Size

BobBridges · 04-13-2020, 08:59 PM

I see something in your program that makes me afraid to try it for myself, but I think I can describe it and you tell me whether I'm mistaken.

1) First step makes sense; you're getting the width of the two columns:

totalWidth = Columns("b").ColumnWidth + Columns("c").ColumnWidth

2) I don't understand this part: You start a loop, and tell it to go on doing it over and over as long as your selection takes in cells in exactly two columns. But I don't see that anything inside the loop changes that selection, so the program will go on running, redoing the loop again and again.

Do While Selection.Columns.Count = 2
Loop

3) What you do inside the loop looks like it started out to make sense: You want to adjust the width of column C to make B+C come out to the desired width. Looks to me, though, as if you made it actually come out to the same width it already was. That's because you're using the actual width instead of the desired width:

columnBWidth = Columns("b").ColumnWidth
columnCAdjustedWidth = totalWidth - columnBWidth
Columns("c:c").ColumnWidth = columnCAdjustedWidth

4) I also don't get what the Wait statement is for, but I don't think it's hurting anything. Excel seems to freeze, as I see it, because it's running the loop over and over again.

Here's what I would do:

Code:

Sub CommandButton1_Click()
  Columns(3).ColumnWidth = 539 - Columns(2).ColumnWidth
  End Sub

Nothing wrong with using Columns("B") (or is it "B:B"?) if you want; I'm used to usng numbers, is all.