如何通过列标题名称重新排列Excel列 [英] How to rearrange the excel columns by the columns header name

问题描述

如何通过列标题名称重新排列列.我需要每周处理一次报告，原始工作表有23列.我将其中的一部分作为示例进行摘要.

How could I rearrange the columns by the columns header name. I need to process a report on weekly basis,the raw worksheet has 23 columns.I abstract a part of them as example.

原始列的顺序:数量(QTY)支付条款合同编号等
所需的列顺序:合同号付款条件数量等

有什么想法如何使用VBA代码自动进行列重排吗?预先非常感谢.

Any idea how to automatize the columns rearrangement with VBA code? Many thanks in advance.

推荐答案

这对您有何作用?我只尝试了四列，它对我有用.

How does this work for you? I tried with just four columns, and it worked for me.

Sub rearrange_Columns()
Dim correctOrder() As Variant
Dim lastCol As Long
Dim headerRng As Range, cel As Range
Dim mainWS As Worksheet

Set mainWS = ActiveWorkbook.Worksheets("Sheet1")

' Edit this to be the correct order you need
correctOrder() = Array("Column A", "Column B", "Column C", "Column D")
' Now, we know that the number of headers you have to rearrange are `UBound(CorrectOrder)+1`


With mainWS
    lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    Set headerRng = .Range(.Cells(1, 1), .Cells(1, lastCol))
End With

Dim newWS As Worksheet
Set newWS = ActiveWorkbook.Sheets.Add
newWS.Name = "Rearranged Sheet"

Dim col As Long
With newWS
    For col = 1 To lastCol
        For Each cel In headerRng
            If cel.Value = correctOrder(col - 1) Then
                mainWS.Columns(cel.Column).Copy .Columns(col)
                Exit For
            End If
        Next cel
    Next col
End With

End Sub

注意:没有真正的错误处理，因此，如果需要对标头进行 TRIM()或检查错误信息，则需要添加一些功能来实现此目的.

Note: There's no real error handling, so if your headers need to be TRIM()ed or checked for erroneous info, you'll need to add something that does that.

这篇关于如何通过列标题名称重新排列Excel列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！