Application.Union re-ordering

[snowmantle]

Hi,

I am using Application.Union to combine several 1 column ranges together.

However I want to be able to order them in order the arguments are supplied to the method and not in the original order the columns were in. Is this possible?

Example:

Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10

Application.Union(Col1, Col7, Col5, Col3, Col10)

And the result of the union is put in a new sheet with the cols in this order

Col1, Col7, Col5, Col3, Col10

I know I could just do it some other way but the existing code already uses this union and I do not have much time.

I welcome other suggestions if this just is not going to be possible using the union method.

Thanks

 

[snowmantle]

At the moment the union returns the cols in the original order

e.g.

Col1, Col3, Col5, Col7, Col10

 

[snowmantle]

Actually I am just thinking that Range(Col1, Col7, Col5 ...)

will work but I will have to test that later when I get time to work on this.

 

[SkipVought]

I want to be able to order them in order the arguments are supplied to the method and not in the original order the columns were in.

I believe that the order has absolutely nothing to do with how this method works. It just joins them in a UNION. It's a "black box."

What did you hope to gain by changing the order?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[snowmantle]

I have lots of files that all need sanitising and putting into an Access db.

A lot of the files have the columns in different orders, I find all the relevant correct columns and then create a collection object called "filters" and add each column (range) to it.

The do the union like below

    Set tmpRng = Application.Union(filters.Item("SPCODE"), filters.Item("PCODE"), filters.Item("CATEGORY"), _
    filters.Item("FACTCODE"), filters.Item("FACTSP"))

TmpRng will be a union of the ranges in the original order they were in.

tmpRng is added to a new worksheet and filtered for unique values as well incorrect values get edited.

I take the below approach for updating the db. You can see that if the srcRng columns are not always in the same order the wrong values will go into the db.

            .AddNew
            .Fields(0) = Trim(CStr(srcRng.Cells(r, 1).Value))
            .Fields(1) = Trim(CStr(srcRng.Cells(r, 3).Value))
            .Fields(2) = Trim(CStr(srcRng.Cells(r, 5).Value))
            .Fields(3) = Trim(CStr(srcRng.Cells(r, 2).Value))
            .Fields(4) = Trim(CStr(srcRng.Cells(r, 4).Value))

 

[snowmantle]

An approach I was probably going to take, which hopefully is quick.. would be to write a function to find the range by its title.. but then I need to know the col number for its address.

E.g. GetColNumByTitle("SPCODE")

returns 1

Then I can do

srcRng.Cells(r, GetColNumByTitle("SPCODE").Value

just not sure how to get the correct column number. Since Address property gives you a bit more than that.

I guess I could add the title names into an array and then use its position in the array.

 

[SkipVought]

In that case I'd use a mapping table:
[tt]
FromSht FromCol ToSht ToCol
[/tt]
One simple set of code can be made to process any from/to set.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[snowmantle]

Wasnt sure how to approach this so just did this

Function GetColNumByTitle(rng As Range, titleName As String) As Integer
'finds a title within row 1 of a range
'returns 0 if it cant be found
    Dim colCount, ColNum As Integer
    Dim titleStart, titleEnd, FindTitleCell As Range
    colCount = rng.Columns.Count
    'set start and end of the title row of the range supplied
    Set titleStart = rng.Cells(1, 1)
    Set titleEnd = rng.Cells(1, colCount)

    'check through the title row of the given range
    With Range(titleStart, titleEnd)
        'find the title value we are looking for within the title range
        Set FindTitleCell = .Find(What:=titleName, lookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False)
    End With
    If FindTitleCell Is Nothing Then
        ColNum = 0
    Else
        ColNum = FindTitleCell.Column
    End If
    
    GetColNumByTitle = ColNum
End Function