Autofit last column in a table

[TenDeadGoats]

Hi Guys,

I think this one might be pretty simple but I've been struggling with it a little bit. I just figured out how to insert a column onto the end of my table and populate it with the data I want. I just need to figure out how to autofit it now. I think I'm pretty close. Here is what I have so far.

Sub Table_Insert()    
    
Dim LastColumn As Integer
Dim FitRange As Range


    ActiveSheet.ListObjects("Table1").ListColumns.Add
    
    LastColumn = ActiveSheet.ListObjects("Table1").Range.Columns.Count
    
    ActiveSheet.ListObjects("Table1").HeaderRowRange(LastColumn).Select
    
    ActiveCell.FormulaR1C1 = "CLIENT NAME"
    
    ActiveCell.Offset(1, 0).Select
    
    ActiveCell.FormulaR1C1 = "=TRIM(UPPER(SUBSTITUTE(R[0]C[-13],""."","""")))"
    
    ActiveSheet.ListObjects("Table1").ListColumns(LastColumn).Range.Select
    
    Set FitRange = Selection
    
    Worksheets("Commissions Data").Range(FitRange).Columns.AutoFit
    
    
End Sub

Thanks for the help!

[paulked]

Try this

Sub Table_Insert()
    
Dim LastColumn As Integer


    ActiveSheet.ListObjects("Table1").ListColumns.Add
    
    LastColumn = ActiveSheet.ListObjects("Table1").Range.Columns.Count
    
    ActiveSheet.ListObjects("Table1").HeaderRowRange(LastColumn).Select
    
    ActiveCell.FormulaR1C1 = "CLIENT NAME"
    
    ActiveCell.Offset(1, 0).Select
    
    ActiveCell.FormulaR1C1 = "=TRIM(UPPER(SUBSTITUTE(R[0]C[-13],""."","""")))"
    
    ActiveCell.Columns.EntireColumn.AutoFit

End Sub

[paulked]

Or this is a bit slicker:

Sub Table_Insert()
    Dim tbl As ListObject
    Set tbl = ActiveSheet.ListObjects("Table1")
    With tbl
        .ListColumns.Add
        .HeaderRowRange(.Range.Columns.Count) = "Client Name"
        .HeaderRowRange(.Range.Columns.Count).Offset(1).Formula = "=TRIM(UPPER(SUBSTITUTE(R[0]C[-13],""."","""")))"
        .HeaderRowRange(.Range.Columns.Count).Columns.EntireColumn.AutoFit
    End With
End Sub

[p45cal]

how about:

Sub Table_Insert()
With ActiveSheet.ListObjects("Table1").ListColumns.Add
  .Range.Cells(1) = "Client Name" 'there is no HeaderRowRange object exposed for a listColumn!
  .DataBodyRange.FormulaR1C1 = "=TRIM(UPPER(SUBSTITUTE(R[0]C[-13],""."","""")))"
  .Range.Columns.AutoFit    'looks only at cells within the table
  '.Range.EntireColumn.AutoFit 'looks at whole sheet column
End With
End Sub

?

[paulked]

Yes, tables are another mine for me to explore in more depth, but after I'm happy with arrays.

Incidentally, I've been playing with dates in arrays between pc's. One set up for US and one UK. I'm still trying all the possible permutations (around my confusion sometimes!) but using CDbl seems to work every time. Thanks for the insight, I've learnt a lot because of it

[TenDeadGoats]

Hi Paul,

That is absolutely perfect. It does exactly what I want and it's 10x cleaner than the code I had before. One general question for you, I have noticed a lot of VBA programmers such as yourself will write the code within a "With/End With" framework rather than just writing it in directly like I have been doing. What is the benefit to this? I want to try and learn with good habits if I can.

[paulked]

Although they do almost the same thing, Pascals code is better, but glad to have helped.

With / End With can save a lot of typing! See here for more details.