Pass Worksheet to a function to autofit columns

user_19839348 0
Viewed 35

I have the following working code as part of a bigger function

Dim SheetToModify As Worksheet
Set SheetToModify = Sheets(strNewProjSheetName)
Dim x As Integer

For x = 1 To SheetToModify.UsedRange.Columns.Count
    SheetToModify.Columns(x).EntireColumn.AutoFit
Next x

Where strNewProjSheetName is a string.

I'd like to extract the for loop in to a function. To do this I have

Public Function AutofitAllUsedColumns(mySheet As Worksheet)
    Dim x As Integer

For x = 1 To mySheet.UsedRange.Columns.Count
     mySheet.Columns(x).EntireColumn.AutoFit
Next x

End Function

and call it with the following

Dim SheetToModify As Worksheet
Set SheetToModify = Sheets(strNewProjSheetName)

AutofitAllUsedColumns (SheetToModify)

Unfortunately, this gives the error Object doesn't support this property or method, any help will be appreciated.

excel-formula vba excel
1 Answers

Autofit Used Columns

  • Note that

    mySheet.Columns(x).EntireColumn.AutoFit

    refers to the columns of the worksheet which are only the same as the used range columns if the used range starts in column A. The correct line would be:

    mySheet.UsedRange.Columns(x).EntireColumn.AutoFit

  • A function returns a result while a sub doesn't. AutofitAllUsedColumns is not a function, it's a sub.

  • You don't need a loop for this kind of operation. The code is more efficient without it.

  • Since it has only one line, its purpose is questionable i.e. you can just use

    SheetToModify.UsedRange.EntireColumn.AutoFit

    in the calling procedure.

Option Explicit

Sub AutoFitTEST()
    
    Const strNewProjSheetName As String = "Sheet1"
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim SheetToModify As Worksheet
    Set SheetToModify = wb.Worksheets(strNewProjSheetName)
    
    AutofitAllUsedColumns SheetToModify

End Sub

Sub AutofitAllUsedColumns(ByVal mySheet As Worksheet)
    mySheet.UsedRange.EntireColumn.AutoFit
End Sub
user_98140690
Related