VBA: Order Columns by Column Header

SteveOranjin

Board Regular
Joined
Dec 18, 2017
Messages
170
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

Code:
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,861
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
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
 
Last edited:

SteveOranjin

Board Regular
Joined
Dec 18, 2017
Messages
170
Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,861
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Dec 18, 2017
Messages
170
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,142
Messages
5,640,376
Members
417,139
Latest member
bdmprasenjit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top