根据标题名称移动列 [英] Moving Columns based on header name

问题描述

我有一个宏，可以将列重新排列为特定顺序.

I have a macro that rearranges the columns into a particular order.

Sub ArrangeColumns()

' ArrangeColumns Macro

    Columns("C:C").Select
    Application.CutCopyMode = False
    Selection.Cut
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight
    Columns("K:K").Select
    Selection.Cut
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    Columns("H:H").Select
    Selection.Cut
    Columns("G:G").Select
    Selection.Insert Shift:=xlToRight
    Columns("J:J").Select
    Selection.Cut
    Columns("H:H").Select
    Selection.Insert Shift:=xlToRight
    Columns("J:J").Select
    Selection.Cut
    Columns("I:I").Select
    Selection.Insert Shift:=xlToRight
    Columns("K:K").Select
    Selection.Cut
    Columns("J:J").Select
    Selection.Insert Shift:=xlToRight
    Range("P11").Select
End Sub

这不再起作用，因为无法再保证原始数据的列处于特定顺序.

This no longer works because the columns of the raw data can no longer be guaranteed to be in a specific order.

有没有办法重写上面的代码(是，它是由记录宏"创建的)，以它们的列标题名称替换行"Columns("C:C")", Columns("A:A")"等?

Is there a way that I can rewrite the above code (Yes it was created by "Record Macro") to replace the lines "Columns("C:C")", Columns("A:A")", etc. with their column header names?

有没有更好的方法可以解决这个问题?

Even better is there a better approach to this problem?

推荐答案

如果您知道所有标题名称，则可以定义标题名称的数组，并使用该数组的索引在各列之间移动.

If you know all the header names, you can define an array of the header names and use the array's index to move the columns around.

Sub columnOrder()
Dim search As Range
Dim cnt As Integer
Dim colOrdr As Variant
Dim indx As Integer

colOrdr = Array("id", "last_name", "first_name", "gender", "email", "ip_address") 'define column order with header names here

cnt = 1


For indx = LBound(colOrdr) To UBound(colOrdr)
    Set search = Rows("1:1").Find(colOrdr(indx), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
    If Not search Is Nothing Then
        If search.Column <> cnt Then
            search.EntireColumn.Cut
            Columns(cnt).Insert Shift:=xlToRight
            Application.CutCopyMode = False
        End If
    cnt = cnt + 1
    End If
Next indx
End Sub

任何未在数组中命名的列都将出现在命名列的右侧.

Any column not named in the array will appear on the right of the ones named.

这篇关于根据标题名称移动列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！