VBA: Order Columns by Column Header

[SteveOranjin]

Hello,

I have the below formula which was working quite nicely earlier. I brought it from one sheet and pasted it into a data sheet that I do work with. This is meant to help me work through data by organizing the columns of data. I want to create other VBA Macros that will insert formulas next to these columns based off of their header.

I'm not quite sure what the heck this thing is doing. I think it is only reorganizing columns I and J or something.

What I was trying to get it to do is

Reorder all columns (and associated data below the headers). based off of the order you'll see there. I'd like to see this work to regardless of whether or not all of the columns are actually present. AND if there are any other columns that do not conform to the headers in the code, then I'd like those columns to just be slapped on at the end. Any particular order for those is find as long as the integrity of the data is maintained.

Please help!

Thanks so much!

Steve

Sub Reoder_Columns_Based_Off_Of_Header()Dim rng As Range
Dim i As Integer
Dim J As Integer
Dim Temp
Dim nams As Variant
Dim F
Dim Dex As Integer
nams = Array("Brandname", "Brandcode", "Sku", "UPC", "productName", "Product Name", "msrp", "MSRP", "mapprice", "dnprice", "Category", "mCategory", "mSubCategory", "Model", "mFinish", "Finish", "finish", "Descirption", "Marketing Copy", "CollectionName", "Length", "Width", "Height", "Weight", "dimensionstring", "ship length", "ship width", "ship height", "shipWeight", "shipstring", "Bullet1", "Bullet2", "Bullet3", "Bullet4", "Bullet5", "speclink", "installlink", "imageName", "Imagelink", "VideoLink1", "Oversized")
Set rng = Range("A1").CurrentRegion
For i = 1 To rng.Columns.Count
    For J = i To rng.Columns.Count
        For F = 0 To UBound(nams)
            If nams(F) = rng(J) Then Dex = F: Exit For
        Next F
        If F < i Then
            Temp = rng.Columns(i).Value
            rng(i).Resize(rng.Rows.Count) = rng.Columns(J).Value
            rng(J).Resize(rng.Rows.Count) = Temp
        End If
    Next J
Next i


End Sub

 

[Fluff]

How about

Sub Reoder_Columns_Based_Off_Of_Header()
   
   Dim Fnd As Range
   Dim i As Integer
   Dim nams As Variant
   
   For Each nams In Array("PostCode", "In Use?", "Easting", "Northing", "GridRef", "Introduced", "Terminated", "Ward", "Ward Code", "Parish", "District", "District code", "Longitude", "Latitude", "Population", "Nationalpark")
      Set Fnd = Range("1:1").Find(nams, , , xlWhole, , , False, , False)
      If Not Fnd Is Nothing Then
         i = i + 1
         If Not Fnd.Column = i Then
            Fnd.EntireColumn.Cut
            Columns(i).Insert
         End If
      End If
   Next nams

End Sub

 

[SteveOranjin]

Sub Reoder_Columns_Based_Off_Of_Header()   
   Dim Fnd As Range
   Dim i As Integer
   Dim nams As Variant
   
   For Each nams In Array("Brandname", "Brandcode", "Sku", "UPC", "productName", "Product Name", "msrp", "MSRP", "mapprice", "dnprice", "mCategory", "mSubCategory", "Category", "category", "SubCategory", "subcategory", "Sub Category", "sub category", "Model", "model", "mFinish", "finish", "Finish", "Description", "Marketing Copy", "CollectionName", "Length", "Width", "Height", "Weight", "dimensionstring", "ship length", "ship height", "ship weight", "shipstring", "Bullet1", "Bullet2", "Bullet3", "Bullet4", "Bullet5", "speclink", "installink", "Imagelink", "VideoLink1", "Oversized")
      Set Fnd = Range("1:1").Find(nams, , , xlWhole, , , False, , False)
      If Not Fnd Is Nothing Then
         i = i + 1
         If Not Fnd.Column = i Then
            Fnd.EntireColumn.Cut
            Columns(i).Insert
         End If
      End If
   Next nams


End Sub

I'm not sure if there is something you are doing that i'm not, but this seems to just order columns as they are. If I add a random column name into the columns, it just pushes it over a bunch. Additionally, if there is a blank column it just sits there.

If I'm pushing it, let me know.

So if the column headers are (Case1) "A","B","David","F","D","C","E". The Macro should have the following order programmed into it. "A","B","C","D","E". In the case of case 1, it should wind up working out like this, "A","B","C","D","E","F","David".

I don't see why this macro doesn't do it that.

Let me know if I'm pushing too much.

Steve

 

[Fluff]

The headers should end up in the order shown in the macro.
Are the values in the code exactly the same as the headers in the sheet (including leading/trailing spaces)?
Also do you have any merged cells?

 

[SteveOranjin]

The headers should end up in the order shown in the macro.
Are the values in the code exactly the same as the headers in the sheet (including leading/trailing spaces)?
Also do you have any merged cells?

Not that I know of. Maybe there was some discrepancy between the two. I'll give it a look this evening and get back to you.

Steve