Rearrange columns

[k3vsmith]

I have a macro that is working perfectly - if the columns are in the correct order. Ive recently found there are users that produce the report with different column orders (same columns, just different order). I want to enhance the macro to first check if the columns are in same order. If not, readjust columns. Then perform rest of macro.

In the beginning of the macro the constants have been identified (in the correct order):

Const cnInputProjectID = "a1"
Const cnInputActivityId = "b1"
Const cnInputActivityStatus = "c1"
Const cnInputActivityName = "d1"
Const cnInputStartDate = "e1"
Const cnInputFinishDate = "f1"
Const cnInputActualStart = "g1"
Const cnInputActualFinish = "h1"
Const cnInputBaselineStart = "i1"
Const cnInputBaselineFinish = "j1"
Const cnSpacer = "k1"
Const cnInputFSMonth = "l1"
Const cnInputFFMonth = "m1"
Const cnInputASMonth = "n1"
Const cnInputAFMonth = "o1"
Const cnInputBSMonth = "p1"
Const cnInputBFMonth = "q1"

Can you please assist on how best to go about this?

 

[NARAYANK991]

Hi ,

In case you do not get any answers , please upload your workbook with the data and the macro in it.

Narayan

 

[k3vsmith]

Yes, good suggestion. The file below is an example of what we run macro against. In the above constants, k1-q1 are added by the macro.
I will upload macro as well.

 

[k3vsmith]

Macro is attached.

 

[Marc L]

I don't know the purpose but just using an advanced filter
the output columns order does not matter !
(if exactly same spelling headers between source and output)
​

 

[k3vsmith]

Ok yes, maybe I should restate, the macro works either way but if columns are not in correct order they skew the data. They need to be in the particular order (constants a1 - j1) to get the correct output.

 

[Marc L]

No order issue with advanced filters …
Needs around 10 codelines,
to compare with your the 300 lines of your gas factory code !

Introduction to Excel Advanced Filters

Extract data using Advanced Filter and VBA
​

 

[k3vsmith]

ok Ill check these links out

 

[NARAYANK991]

Hi ,

In case you still want help on the code revision , please post back.

Indicate the name of the macro ; the .xlsm file has many macros , and even though the first of the constants you posted in your opening post is used only in the sbInputGetBEI macro , it will be good to have your confirmation.

Narayan

 

[k3vsmith]

I actually figured out a way to code it in. I was looking for a quick fix. But I actually like the advanced filtering method @Marc L posted. I probably wont use that for this macro but maybe down the road on another project. I thought that technique was pretty neat and something new to me. Heres the code I added:

Option Explicit
Const cncolHdrRow = 1
Dim wsReport As Worksheet

Sub sbCallOrder()
   
    Set wsReport = ActiveSheet
    sbOrderStuff lgColHdrRow:=cncolHdrRow

End Sub
Sub sbOrderStuff(lgColHdrRow As Long)

    'dimenision variables
   
    Dim arColHdrOrder(9) As String
    Dim rgColHeaders As Range
    Dim rgSortOrder As Range
    Dim stAddress As String
    Dim i As Integer
   
   
    'assign variables

    arColHdrOrder(0) = "P_PROJECT_ID"
    arColHdrOrder(1) = "ACTIVITY_ID"
    arColHdrOrder(2) = "ACTIVITY_STATUS"
    arColHdrOrder(3) = "ACTIVITY_NAME"
    arColHdrOrder(4) = "START"
    arColHdrOrder(5) = "FINISH"
    arColHdrOrder(6) = "ACTUAL_START"
    arColHdrOrder(7) = "ACTUAL_FINISH"
    arColHdrOrder(8) = "BASELINE_START"
    arColHdrOrder(9) = "BASELINE_FINISH"
    i = 0


    'insert a header row, find/place order
   
    Set rgColHeaders = wsReport.UsedRange.Rows(lgColHdrRow)
    rgColHeaders.EntireRow.Insert
    Set rgSortOrder = wsReport.UsedRange.Rows(rgColHeaders.Row - 1)

    While i <= UBound(arColHdrOrder)
               
        stAddress = Cells.Find(What:=arColHdrOrder(i), After:=Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, _
              SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Address
        Range(stAddress).Offset(-1, 0).Value = i
           
       
        'increment counter
       
        i = i + 1
   
    Wend

       
    'sort, delete the working column
   
    wsReport.UsedRange.Sort key1:=rgSortOrder, order1:=xlAscending, Orientation:=xlSortRows, Header:=xlNo
    Range("A1").EntireRow.Delete

End Sub

 

[k3vsmith]

Thanks @Marc L for the new technique I can try. And thank you @NARAYANK991 for the responses.