VBA Help with Macro to autofit visible columns starting at row 4

[Steve454]

I have 151 columns (B:EV) containing data coded for insert into member correspondence. For each letter certain columns are hidden, others visible and the visible data in them inserted into each letter. I need to autofit the visible columns because these are being used to validate the data by analysts. I'm very new to VBA so have found something on the Internet to fit my needs. However, this seems to make certain columns too narrow, cutting off the data. Can anyone help me fine-tune this so that the auto-fit doesn't cut off data in a cell?:banghead:


Sub AFitCols()
'*
'** Autofit starting at row 4 (IQC is the WorkSheet name).
'*
Dim MaxRows As Integer
Dim MyRng As Variant
Dim A As Range

ActiveWorkbook.Sheets(1).Activate
MaxRows = ActiveSheet.UsedRange.Rows.Count - 4
'*
'** We want colums B to EV, so in the Cells we specify 1 to 151.
'*
MyRng = Range(Cells(4, 2), Cells(MaxRows, 151)).SpecialCells(xlCellTypeVisible).Address
Range(MyRng).Select
Range(MyRng).Columns.AutoFit

Range("B4").Select

End Sub 'AFitCols

[mdmackillop]

Your selection was cutting off the bottom 4 rows, corrected here which may fix your issue. I've also added in a loop to increase column width by 2, if required.

Sub AFitCols() '*
'** Autofit starting at row 4 (IQC is the WorkSheet name).
'*
Dim MaxRows As Long
Dim MyRng As Variant
Dim A As Range, Cel As Range

ActiveWorkbook.Sheets(1).Activate
MaxRows = ActiveSheet.UsedRange.Rows.Count - 4
'*
'** We want colums B to EV, so in the Cells we specify 1 to 151.
'*
Set MyRng = Range(Cells(1, 2), Cells(MaxRows, 151)).Offset(4).SpecialCells(xlCellTypeVisible)
MyRng.Columns.AutoFit
'*
'** Increase columnn widths by set amount
'*
For Each Cel In MyRng.Rows(1).Cells
Cel.ColumnWidth = Cel.ColumnWidth + 2
Next
Range("B4").Select

End Sub 'AFitCols

[Steve454]

Your selection was cutting off the bottom 4 rows, corrected here which may fix your issue. I've also added in a loop to increase column width by 2, if required.

Sub AFitCols() '*
'** Autofit starting at row 4 (IQC is the WorkSheet name).
'*
Dim MaxRows As Long
Dim MyRng As Variant
Dim A As Range, Cel As Range

ActiveWorkbook.Sheets(1).Activate
MaxRows = ActiveSheet.UsedRange.Rows.Count - 4
'*
'** We want colums B to EV, so in the Cells we specify 1 to 151.
'*
Set MyRng = Range(Cells(1, 2), Cells(MaxRows, 151)).Offset(4).SpecialCells(xlCellTypeVisible)
MyRng.Columns.AutoFit
'*
'** Increase columnn widths by set amount
'*
For Each Cel In MyRng.Rows(1).Cells
Cel.ColumnWidth = Cel.ColumnWidth + 2
Next
Range("B4").Select

End Sub 'AFitCols

Thanks so much mdmackillop!!! This works great! You're a life-saver! :clap: :clap: :clap: