VBA如何基于指定列对单元格区域重新排序

1、在Excel文件中，同时按下组合按钮【alt+F11】，打开vba编辑器。

2、在编辑器窗口中，插入一个模块，然后在模块内输入如下代码。Option Explic坡纠课柩itSub sample_sort烫喇霰嘴() '根据品号列重新排序 Dim row_ini As Integer, row_test As Integer, number As Integer Dim name_sample As String, ii As Integer, flag As Integer Dim row_temp As Integer, row_object As Integer row_ini = 2 '测试数据从第2行开始 (第1行是标题行) row_test = Cells(Rows.Count, 3).End(xlUp).Row '测试数据最后一行的行号 number = 5 '测试点数目，包括无需测的测试点。 name_sample = "SAM21-123" '样品名称 '1. 根据“品号”列查找测试数据 For ii = 1 To number row_temp = row_test + 1 + ii Cells(row_temp, 3) = "SAM21-123" & "-" & CStr(ii) '输入样品单号 Cells(row_temp, 4).Formula = "=IFERROR(MATCH(C" & CStr(row_temp) & ",C:C,0),10000)" '例："=IFERROR(MATCH(C6,C:C,0),10000)" row_object = Cells(row_temp, 4).Value If Cells(row_temp, 4).Value <= row_test Then '复制目标行到指定区域 Rows(row_object).Copy Rows(row_temp).Select ActiveSheet.Paste Else Cells(row_temp, 4).Formula = "" End If Next ii '2. 覆盖原有的测试数据 Rows(row_test + 2 & ":" & row_test + 2 + number + row_test - row_ini + 1).Copy Rows(row_ini).Select ActiveSheet.Paste MsgBox "Done!" Exit SubEnd Sub

3、在"测试数据"表内，运行上述的宏命令，就可以得到经过重新排序后的测试数据。