Set minumun for column width macro

[noora19]

Hello,

I am trying to set a minimum column width for a macro that uses the autofit code. I tried this, but it doesn't seem to work:

With ActiveSheet.Columns.AutoFit
If Columns.ColumnWidth < 10 Then
Columns.ColumnWidth = 10
End If
End With


If possible my preference was to adjust specific columns to 10. Any advice?

Thanks!

 

[Warship]

Try:

Sub ColumnMinTen()
  ActiveSheet.Columns.AutoFit
  For Each c In Range("1:1")
    If c.ColumnWidth < 10 Then c.ColumnWidth = 10
  Next
End Sub

 

[Trebor76]

If possible my preference was to adjust specific columns to 10

Have a look at the following whch sets the column width for Col's B, C, E and F (change to suit your needs) to 10 via the UNION (VBA only) function:

Union(Columns("B:C"), Columns("E"), Columns("F")).ColumnWidth = 10

HTH

Robert

 

[noora19]

I am having a bit trouble implement either code to my existing macro (which is fairly complex). The piece below is part of an IF statement:

With ActiveSheet.Columns.AutoFit
End With

What can i insert in the middle of this to make it work? Can I just specify something similar to:

Range.Columns("C:N").Columnwidth=10

?

So far it hasn't worked.

 

[Trebor76]

Either this...

Union(Columns("C"), Columns("C:N")).ColumnWidth = 10

...or this:

Range("C:N").EntireColumn.ColumnWidth = 10

will do the job.

 

[Rick Rothstein]

Have a look at the following whch sets the column width for Col's B, C, E and F (change to suit your needs) to 10 via the UNION (VBA only) function:

Union(Columns("B:C"), Columns("E"), Columns("F")).ColumnWidth = 10

You can avoid the Union function and do this with a straight Range property call...

Range("B:C,E:F").ColumnWidth = 10

You can also do single columns mixed with contiguous column ranges as well. For example, let's say you wanted to set the column width for Columns B:C, E, H and M:P...

Range("B:C,E:E,H:H,M:P").ColumnWidth = 10

Note the need to "double up" the single column references so that they too include a colon like the contiguous ranges do.

 

[GTO]

Just another suggestion, in case which columns will initially be too skinny is an unknown:

Sub AdjustCols()
Dim rngCol As Range
    
    For Each rngCol In UsedRange.Columns
        rngCol.EntireColumn.ColumnWidth = Application.Max(rngCol.EntireColumn.ColumnWidth, 10)
    Next
End Sub

 

[noora19]

Thanks everyone for contributing! The codes you all provided seem to work in isolation. I just need to figure out why they are not working with the rest of my code. My columns do get autofitted--but unable to set them to 10.

 

[GTO]

If the columns are getting auto-fitted, it would seem that the correct sheet is being referenced. Could you show us enough of the code to see how the sheet and range are being referenced when the auto-fit occurs?

 

[noora19]

Here is the column autfit statement embedded in an IF statement that changes all cells in the worksheet to pasted values. I also have a page set up/format code--but that I have already troubleshooted and it works well in conjunction with the column code that incorporated the width of 10.


If Destwb.Sheets(1).ProtectContents = False Then
With Destwb.Sheets(1).UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With

With ActiveSheet.Columns.AutoFit
End With

Application.CutCopyMode = False
End If