PDA

View Full Version : [SOLVED] Autofit last column in a table



TenDeadGoats
05-05-2020, 12:13 PM
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
05-05-2020, 11:13 PM
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
05-06-2020, 07:45 AM
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
05-07-2020, 04:33 AM
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
05-07-2020, 05:13 AM
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 :thumb

TenDeadGoats
05-07-2020, 10:49 AM
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
05-07-2020, 11:00 AM
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 (https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/with-end-with-statement) for more details.