CODEWITHSUNDEEP
excelvba
Mark S.
Mark S.

Reputation: 2594

Reformat List of Table Columns by Header

I'm sure there's something obvious I'm missing, so any help is appreciated. I'm working with a Data Table in a Table Format. I have a few headers I'd like to reformat in a General number format. I'm unsure if the data when it's dropped in will remain the same so I figure I'd like to work off of names rather than the column numbers or letters. This is part of a bigger project, but this is the first steps so the rest is irrelevant for now.

When I run this I get the error

Run-Time error 438 Object doesn't support this property or method

on the .Union block.

Dim Table As Excel.ListObject
Dim SortColIndex As Long
Dim SortCol As Range

Private Sub DefineVars()

Set Table = ActiveWorkbook.Worksheets("Data").ListObjects("_Data")
SortColIndex = Table.ListColumns("Bill to Account Number").Index
Set SortCol = Table.ListColumns(SortColIndex).Range

End Sub

Sub Fedex()

Call DefineVars

With Table
    .Union(.ListColumns("Bill to Account Number").DataBodyRange, _
    .ListColumns("Net Charge Amount").DataBodyRange, _
    .ListColumns("Tracking ID Charge Amount").DataBodyRange, _
    .ListColumns("Tracking ID Charge Amount9").DataBodyRange, _
    .ListColumns("Tracking ID Charge Amount11").DataBodyRange).Select
End With
With Selection
    .NumberFormat = "General"
End With

With Table
    .Sort.SortFields.Clear
    .Sort.SortFields.Add _
        Key:=SortCol, _
        SortOn:=xlSortOnValues, _
        Order:=xlDescending
    With .Sort
    .Header = xlYes
    .Apply
    End With
End With

End Sub

Upvotes: 0

Views: 436

Answers (1)

Shai Rado
Shai Rado

Reputation: 33682

Try the code below, I've added explanation inside the code's comments:

Option Explicit

Sub Fedex()

Dim Tbl As ListObject
Dim TblRng As Range

'Call DefineVars

' set the ListObject (assuming you have 1 table in your worksheet)
Set Tbl = Worksheets("Sheet1").ListObjects(1) ' rename according to your sheet's name

With Tbl
    ' set the Range inside the ListObject (don't use Select)
    Set TblRng = Application.Union(.ListColumns("Bill to Account Number").DataBodyRange, _
                .ListColumns("Net Charge Amount").DataBodyRange, _
                .ListColumns("Tracking ID Charge Amount").DataBodyRange, _
                .ListColumns("Tracking ID Charge Amount9").DataBodyRange, _
                .ListColumns("Tracking ID Charge Amount11").DataBodyRange)
End With
With TblRng
    .NumberFormat = "General"
End With

' rest of your code ...

End Sub

Upvotes: 1

Related Questions