Steve454
09-05-2017, 01:59 PM
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
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