How to sort the last column containing data in VBA

[Minerva7]

Hello,

I'm looking for help in updating my VBA so that I'm accounting for the last column with data. Here's what I have, and it works fine for static data, but the column changes each day (moves to the right in a dated table range). Is there a way to sort the last column containing data, using VBA, when the last column changes each day? Thanks in advance.

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range( _
        "B5:B24"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
       .SetRange Range("A5:X24")
       .Header = xlGuess
       .MatchCase = False
       .Orientation = xlTopToBottom
       .SortMethod = xlPinYin
       .Apply
    End With

 

[JoeMo]

Is there a way to sort the last column containing data, using VBA, when the last column changes each day?

This will sort the last column if there is a header in the first row. If your layout differs from this, you will need to modify the code.

Sub SortLastColumn()
'assumes row 1 is a header row for the last column
Dim Lcol As Long, ColLetter As String
Lcol = Cells(1, Columns.Count).End(xlToLeft).Column
ColLetter = Split(Columns(Lcol).Cells(1).Address(1, 1), "$")(1)
Range(ColLetter & ":" & ColLetter).Sort key1:=Range(ColLetter & "2"), order1:=xlDescending, Header:=xlYes
End Sub

 

[Minerva7]

Thanks very much for the help! How would I adjust this code if there are no headers in the first column? I have a list of departments in the first column and dates along the top of the table. Each departments tickets are entered in the appropriate dated column each day. The entire range (with date/headers at the top and department names in the first column) is A5:X24.

 

[Minerva7]

I probably should have mentioned I'm sorting values by largest to smallest

 

[JoeMo]

Thanks very much for the help! How would I adjust this code if there are no headers in the first column? I have a list of departments in the first column and dates along the top of the table. Each departments tickets are entered in the appropriate dated column each day. The entire range (with date/headers at the top and department names in the first column) is A5:X24.

Sounds like you have Date headers in the first row. Would that be row 5? Can you post an example of what your data layout looks like using XL2BB? And .... do you want to sort just the last column or do you want the sort of the last column to affect all the columns to the left of the last column as well?

 

[Minerva7]

I'm looking to sort the last column containing data to affect all columns to the left of the last column. Here is an example of what the table looks like. Each day brings in new values in a new column to the right. I apologize I'm not familiar with XL2BB, but here is a screenshot of a generic version of this table. I'm hoping this helps. The department names on the left contain links to their respective tabs/sheets within the same workbook. The last empty column is 'X' and the 'Row Labels' cell is at A5. I have a separate macro the populates these values into the next empty column each day. I just need to sort by largest to smallest.

Row Labels

13-Aug

14-Aug

17-Aug

18-Aug

19-Aug

20-Aug

21-Aug

24-Aug

25-Aug

26-Aug

27-Aug

28-Aug

31-Aug

1-Sep

2-Sep

Total

389

375

295​

295​

289​

282​

278​

236​

206​

199​

191​

182​

178​

164​

163​

Department 1

5

5

5​

5​

5​

5​

5​

2​

2​

2​

2​

2​

2​

2​

2​

Departmnet 2

145

142​

79​

79​

78​

77​

74​

67​

37​

36​

35​

33​

31​

26​

26​

Department 3

32

35

35​

35​

34​

33​

33​

31​

31​

29​

29​

24​

24​

23​

23​

Department 4

9

9

7​

7​

7​

6​

6​

5​

5​

5​

4​

4​

5​

5​

5​

Department 5

77

70

62​

62​

61​

59​

59​

44​

44​

43​

42​

39​

36​

33​

33​

Department 6

105

98

93​

92​

89​

88​

88​

74​

74​

72​

69​

69​

68​

63​

63​

Department 7

0

0

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

Department 8

0

0

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

Department 9

4

4

2​

2​

2​

2​

2​

1​

1​

1​

1​

1​

1​

1​

1​

Department 10

3

3

3​

5​

5​

4​

3​

2​

2​

2​

1​

2​

2​

2​

1​

Department 11

6

6

6​

5​

5​

5​

5​

9​

9​

8​

7​

7​

7​

7​

8​

Department 12

0

0

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

Department 13

0

0

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

Department 14

2

2

2​

2​

2​

2​

2​

1​

1​

1​

1​

1​

2​

2​

1​

Department 15

1

1

1​

1​

1​

1​

1​

0​

0​

0​

0​

0​

0​

0​

0​

 

[JoeMo]

The data you posted appears to have text entries that look like numbers. If that's the actual case the macro below will still work, but the sort will not be numeric, it will be textual. If they are numbers, the sort will be on the last columns entries and will sort them from largest to smallest.

Sub SortByLastColumn()
'assumes row 6, which appears to have formulas in it, is a "header" row
Dim Lcol As Long, ColLetter As String, LRw As Long
Lcol = Cells(6, Columns.Count).End(xlToLeft).Column
ColLetter = Split(Columns(Lcol).Cells(1).Address(1, 1), "$")(1)
LRw = Cells(Rows.Count, Lcol).End(xlUp).Row
Range("B6:" & ColLetter & LRw).Sort key1:=Cells(7, ColLetter), order1:=xlDescending, Header:=xlYes
End Sub