Excel多列排序宏适配可变行数（超9999行失败）求助

Hey there! The issue you're running into is almost certainly because your current macro uses a fixed row range (like A1:F9999) instead of dynamically detecting how many rows of data you actually have each day. Let's fix that with a simple adjustment to your code.

Here's the Step-by-Step Solution: #

1. First, dynamically find the last row with data
   Instead of hardcoding 9999, we'll use Excel's built-in functions to locate the last row that has content in column A (since your table starts there). This works no matter if you have 10 rows or 100,000 rows.

2. Update your sorting logic to use this dynamic range
   Replace your fixed range with a variable that references the full data set, from the header row (A1) all the way down to that last row we found.

Example Working Code: #

Sub SortDynamicTable()
    Dim lastRow As Long
    Dim ws As Worksheet
    
    ' Set the worksheet you're working with (change "Sheet1" to your actual sheet name)
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Find the last row with data in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Sort the range A1:F[lastRow] by columns A, C, F (ascending order)
    ws.Range("A1:F" & lastRow).Sort _
        Key1:=ws.Range("A1"), Order1:=xlAscending, _
        Key2:=ws.Range("C1"), Order2:=xlAscending, _
        Key3:=ws.Range("F1"), Order3:=xlAscending, _
        Header:=xlYes, ' Make sure this is set to xlYes if you have a header row
        MatchCase:=False, _
        Orientation:=xlTopToBottom
End Sub

Why This Works: #

• ws.Cells(ws.Rows.Count, "A").End(xlUp).Row starts at the very bottom of column A and moves up until it hits the first cell with data—this gives you the exact last row number every time.
• By concatenating lastRow into your range ("A1:F" & lastRow), you ensure the entire table (including all rows of data) is included in the sort, no matter how big it gets.

Quick Tips: #

• Always specify the worksheet (ws variable in the example) instead of relying on the active sheet—this prevents bugs if you accidentally have another sheet selected when running the macro.
• Double-check that Header:=xlYes is correct if your first row is a header; if you don't have a header, change it to xlNo.

Test this with datasets of all sizes (10 rows, 10,000 rows, 50,000 rows) and it should handle everything smoothly!

内容的提问来源于stack exchange，提问作者tgall0163