[SOLVED] Resize column width of a particular column

Resize column width of a particular column

I need to resize excel particular column in a particular sheet.But not able to do so

Sub resizeColumn(sourceWb As String, Sheet As String, column As String)
Dim wkbSource As Workbook
Dim sheetName As Worksheet
Dim columnname As String
Application.ScreenUpdating = False
Set wkbSource = Workbooks.Open(sourceWb)
Set sheetName = wkbSource.Sheets(Sheet).Activate
Worksheets("Column width").Range("A5").ColumnWidth = 16
wkbSource.Close SaveChanges:=True
wkbSource.Close
End Sub

Solution

I think the problem is that you are mixing the names and objects. I assume from your code that you would like to call this with this manner:

Sub CallResizeColumn()
    Call ResizeColumn("PathToYourWorkbook.xlsm", "SheetToResize", "A5")        
End Sub

I have changed a bit your notation by adding a p at the beginning of your parameters (I think it is not a good practice to call them very similar to the keywords). Please keep in mind that you need to use the full reference to your range ("workbook.worksheet.range"). (Note: when you turn off the SreenUpdating, be sure that you turning it back.)

Private Sub ResizeColumn(pSourceWb As String, pSheet As String, pColumn As String)
    Dim wkbSource As Workbook
    Dim shSheet As Worksheet
    Dim rngColumn As Range
    'Application.ScreenUpdating = False
    Set wkbSource = Workbooks.Open(pSourceWb)
    Set shSheet = wkbSource.Sheets(pSheet)
    Set rngColumn = shSheet.Range(pColumn)
    rngColumn.ColumnWidth = 16
    'wkbSource.Close SaveChanges:=True
    wkbSource.Close
End Sub