...扩展区域的数据,如何让整个表按第一列的顺序排列?

发布网友发布时间：2024-01-08 21:17

共2个回答

热心网友时间：2024-08-13 21:18

考虑第二列后数据不知道有多少个，使用vba，将对应第1格的行复制到第2个工作表中

初始第1个表中数据

运行后第2个表中数据

宏

宏：

Sub duiqi()

hangsu = Application.CountA(Worksheets(1).Columns(1))

Worksheets(1).Rows(1).Copy Worksheets(2).Rows(1)

For i = 2 To hangsu

'temp1 = Worksheets(1).Cells(i, 1)

For j = 2 To hangsu

If Worksheets(1).Cells(j, 2) = Worksheets(1).Cells(i, 1) Then

Worksheets(1).Rows(j).Copy Worksheets(2).Rows(i)

Worksheets(2).Cells(i, 1) = Worksheets(1).Cells(i, 1)

j = hangsu + 1

End If

Next j

Next i

End Sub

如果不想在第2个表中显示，宏如下：（注意：运行宏可能导致原数据覆盖，注意备份）

Sub duiqi()

hangsu = Application.CountA(Columns(1))

For i = 2 To hangsu

For j = i To hangsu

If Cells(j, 2) = Cells(i, 1) Then

t1 = Rows(j)

t2 = Cells(i, 1)

temp = Cells(j, 1)

Rows(i).Copy Rows(j)

Cells(j, 1) = temp

Rows(i) = t1