VBA & Macro Problems

Fix an Excel Macro That Does Not Sort All Rows

Khadija KhanKhadija Khan Nov 20, 2026 868 views

Question details

Update an Excel macro so it sorts all rows in an imported CSV file, including newly added records.

Fix an Excel Macro That Does Not Sort All Rows
Product
Microsoft Excel
Device & OS
not provided
Scenario
A recorded macro sorts data after a CSV file is imported, but later imports contain more records than the original file.
Observed behavior
Rows beyond the macro's fixed sort range remain unsorted.
Before you start

Identify the column that determines the sort order and a column populated for every record. Check whether the first row contains headers, and keep the macro in a macro-enabled workbook or Personal Macro Workbook rather than the CSV file.

Solution 1Recommended

Replace the Fixed Sort Range with a Dynamic Range

Use VBA to find the last populated row and sort the full data range after each CSV import.

The example assumes data begins in A1, row 1 contains headers, and column A is populated for every record. It sorts all columns with headers in row 1 by column A in ascending order. Adjust the key, order, and header setting to match your existing sort.

1
Open the macro

In Excel, open the workbook containing the macro, select Developer > Macros, choose the macro, and click Edit. If Developer is not visible, open the VBA editor with Alt+F11 where supported.

2
Update the sort code

Replace the fixed-range sort block with the following code, or adapt its range calculation to your existing sort settings: Dim ws As Worksheet Dim lastRow As Long, lastCol As Long Dim dataRange As Range Set ws = ActiveSheet lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column If lastRow < 2 Then Exit Sub Set dataRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)) dataRange.Sort Key1:=ws.Range("A1"), Order1:=xlAscending, Header:=xlYes

3
Match the imported data

In the VBA editor, change "A" in the lastRow calculation to a column populated for every record if needed. Change Key1, Order1, and Header to match your intended sort. If headers do not cover every data column, adjust the lastCol calculation or set the correct final column.

4
Save and test the macro

Save the macro in a macro-enabled workbook, activate the sheet containing the imported CSV data, and run it from Developer > Macros. Check that the last imported record is in the correct position and that values across each row stayed together.

Replace the Fixed Sort Range with a Dynamic Range
CSV files cannot store VBA: Keep the macro in an .xlsm workbook or the Personal Macro Workbook. Saving the imported data as CSV does not save the macro.
Free Microsoft Office alternative

Sort Imported CSV Data with WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar interface for working with spreadsheets. Open compatible files and sort imported CSV data in WPS Spreadsheets; it does not repair a macro in Microsoft Excel.

Sort imported CSV records using spreadsheet tools.Open and edit Microsoft Excel XLSX files for easier migration.Use a familiar spreadsheet interface.Work with a free, lightweight office suite.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my recorded macro leave new rows unsorted?

The recorded VBA may refer to a fixed range, such as A1:D100. Rows added below that range are excluded until you expand it or calculate the last row dynamically.

How do I sort by a different column?

In the VBA editor, change Key1:=ws.Range("A1") to the header cell of the desired sort column, such as ws.Range("C1"). Keep the full data range selected in the sort so each record stays together.

What if column A has blank cells?

Use a column populated for every record in the lastRow calculation. Otherwise, the calculation can miss records below the last populated cell in column A.

Can I save the macro in the imported CSV file?

No. Save the macro in an .xlsm workbook or the Personal Macro Workbook, then activate the imported data sheet before running it.