VB Code to expand column width

[rizmomin]

Hello:

Please refer to attached file.
I need VB Code to expand the column width as needed.
In example, i have data from column A thru R.
As you can see i have column D, H ,K and R which needs to be expanded by VB Code.
Let me know if you have any questions.
Thanks.
Riz

[shg]

How about just Ctrl+A, Format > Autofit column width. You could record a macro to do that, riz.

[AB33]

If you want a code.

Option Explicit

Sub expandcolumns()
 Columns.AutoFit
End Sub

Or if you want to expand columns to a certain size, say 20

Columns.ColumnWidth = 20

[rizmomin]

Hello AB33:

Using above code will reduce the other column width which is not desirable for me.
Need to come up with logic to check the width of each column and see the ones which needs to be expanded.
Please let me know if you have any questions.
Thanks.

Riz

[AB33]

Columns.AutoFit will adjust each column as needed.
Need to come up with logic to check the width of each column and see the ones which needs to be expanded.
which column/s and what size?

Do you mean

Option Explicit
Sub expandcolumns()
Application.ScreenUpdating = 0
Dim i As Long

With Columns("A:R")
    For i = 1 To .Columns.Count
        If .Columns(i).ColumnWidth < 8.43 Then
          .Columns(i).ColumnWidth = 8.43
        End If
     Next
 End With
 Application.ScreenUpdating = True
End Sub

?
I assumed the normal column size 8.43 and if I column is less than 8.43, the column will be adjusted to 8.43. You need to change the size as per your requirements.

[shg]

Do you mean widen the columns that need it and leave the others alone?

Sub riz()
  Dim rCol          As Range
  Dim dWid          As Double

  For Each rCol In ActiveSheet.UsedRange.EntireColumn
    dWid = rCol.ColumnWidth
    rCol.AutoFit
    If rCol.ColumnWidth < dWid Then rCol.ColumnWidth = dWid
  Next rCol
End Sub

[rizmomin]

Hello shg:

Works great, thanks a lot.
Would it be possible to add code in the above to change all columns of active sheet to certain column range of active sheeet.
Example :

Column D:K

Please let me know if you have any questions.
Thanks.



Riz

[shg]

Would it be possible to add code in the above to change all columns of active sheet to certain column range of active sheeet.
Example :

Column D:K

I don't know what that means, riz.

[Winon]

Hello rizmomin,

Add this line to the bottom of the code supplied by shg, just before End Sub:

Columns("D:K").ColumnWidth = 25   'Change 25 to suit

Regards.

[Winon]

@shg,

Could it be possible that maybe Riz wants it as below?

Option Explicit
Sub riz()
  Dim rCol          As Range
  Dim dWid          As Double

  For Each rCol In ActiveSheet.Columns("D:K").EntireColumn
    rCol.AutoFit
    If rCol.ColumnWidth < dWid Then rCol.ColumnWidth = dWid
  Next rCol
  
End Sub

Kind Regards.

[shg]

You haven't initialized dWid, Winon.

[Winon]

I did that on purpose shg, and testing the Code works fine on my side, even with the Workbook saved as Excel 2003.

Please try it: for example,

[shg]

If you don't initialize it, it's zero, and a column's width is never less than zero, so the test doesn't do anything.

[Winon]

Please try the attached sample Workbook.

Best Regards.

[Winon]

@ shg,

If you copy and pastespecial formats from an unaffected Column into the Range D:K, and run the Macro it works on my side

[xladept]

Just use shg's code on D:K

[shg]

I tried, it Winon. Your code is equivalent to

  Columns("D:K").AutoFit

... is it not?

[Winon]

@ xladept,

It would appear to me that rizmomin wants to Zoom in on Range D:K only, and it is with that assumption, that if that is true, we don't need a loop in the Code.

I guess we will have to wait for confirmation from Riz.

Regards.

[Winon]

@ shg,

Yes, you are correct, and we therefore don't need a loop, if the assumption is correct.

Riz stated;

Would it be possible to add code in the above to change all columns of active sheet to certain column range of active sheeet.
Example :

Column D:K

and you observation;

If you don't initialize it, it's zero, and a column's width is never less than zero

This is already been taken care of by Excels default "Controls", so why bother?

The Loop free and "Reset" code would then be:

Option Explicit
Sub riz()
  
  ActiveSheet.Columns("D:K").EntireColumn.AutoFit
  
End Sub
Sub Reset()

Columns("D:K").ColumnWidth = 8.34   'Change 8.34 to suit

End Sub

[xladept]

The autofit could make the column narrower - that's why I think shg's code is the way to go

[Winon]

@ shg,

... it's zero, and a column's width is never less than zero

Sorry shg, I neglected to mention that, if a column's width is zero, that Column will be hidden by default. in which case one would have to add Columns.Hidden=False to the "Reset Code.

[xladept]

Hi Winon old friend,

I'm taking a back seat now in this one - I hadn't thought of the possibility of zero width