Dynamic use of union function

[Mitchbvi]

I have a routine that asks for non contiguous columns them copies those columns to a new work sheet. The problem I have run into is that the number of columns are not always the same. I have been using a user form that allows selection of cells that allow the column numbers to be passed to the union function. This is the static code

[COLOR=#000000][FONT=UICTFontTextStyleTallBody]Public WellNameCol, ProdDatecol, ProdTypeCol, ProdPriceCol, GrossCol, ExpNameCol, ExpAmountCol, OwnNetCol[/FONT][/COLOR]
[COLOR=#000000][FONT=UICTFontTextStyleTallBody]Sub TestForm()[/FONT][/COLOR]
[COLOR=#000000][FONT=UICTFontTextStyleTallBody]Unload UserForm1[/FONT][/COLOR]
[COLOR=#000000][FONT=UICTFontTextStyleTallBody]sSheet = ActiveSheet.Name[/FONT][/COLOR]
[COLOR=#000000][FONT=UICTFontTextStyleTallBody]ActiveWorkbook.Sheets.Add[/FONT][/COLOR]
[COLOR=#000000][FONT=UICTFontTextStyleTallBody]ActiveSheet.Name = "ActiveColumns"[/FONT][/COLOR]
[COLOR=#000000][FONT=UICTFontTextStyleTallBody]With Worksheets(sSheet)[/FONT][/COLOR]
[COLOR=#000000][FONT=UICTFontTextStyleTallBody]Application.Union(.Columns(WellNameCol), .Columns(ProdDatecol), .Columns(ProdTypeCol), .Columns(ProdPriceCol), .Columns(GrossCol), .Columns(ExpNameCol), _[/FONT][/COLOR]
[COLOR=#000000][FONT=UICTFontTextStyleTallBody].Columns(ExpAmountCol), .Columns(OwnNetCol)).Copy Worksheets("Activecolumns").Range("A1")[/FONT][/COLOR]
[COLOR=#000000][FONT=UICTFontTextStyleTallBody]End With[/FONT][/COLOR]

if one of the variables is missing the with loop fails. I. Just cannot figure out how to build the union dynamically. Any thoughts really appreciated.

Peter

 

[Yongle]

As an alternative to using PUBLIC variables, consider passing an array of column numbers from one sub to the other
- below is a simple example but similar to what you are wanting to do

Sub PassArrayOfColumnNumbers()
[I][COLOR=#006400]'column variables[/COLOR][/I]
    Dim WellNameCol As Long, ProdDatecol As Long, ProdTypeCol As Long, ProdPriceCol As Long
[I][COLOR=#006400]'attribute values somehow[/COLOR][/I]
    WellNameCol = 1: ProdDatecol = 10: ProdTypeCol = 15: ProdPriceCol = 20
[I][COLOR=#006400]'create array of column numbers to copy[/COLOR][/I]
    Dim arr:    arr = Array(WellNameCol, ProdDatecol, ProdTypeCol, ProdPriceCol)
[I][COLOR=#006400]'pass array to another procedure[/COLOR][/I]
    Call CopyColumns(arr)
End Sub

Private Sub CopyColumns(anArray As Variant)
    Dim C As Variant, rng As Range
    With ActiveSheet
        For Each C In anArray
            If rng Is Nothing Then Set rng = .Columns(C) Else Set rng = Union(rng, .Columns(C))
        Next C
    End With
    With ActiveWorkbook.Sheets.Add
        .Name = "ActiveColumns" & Format(Now, "yymmddhhmmss")
        rng.Copy .Range("A1")
    End With
End Sub

You could place all column variables inside the array, attribute values to the ones you want only within the procedure, the others would be set to 0 automatically (because declared as long). Then add condition If C > 0 before setting rng

 

[Mitchbvi]

That worked thank you once again. I had tried setting a couple of the variables to "o" before trying vbnullstring, however I was using the activate rather than initialize event. I have not mentioned it but I work on a Mac and often find solutions that work in Windows do not on my machine. In fact I am still on 2011 because of the lack of support for user forms in the latest Mac version. Not sure if I amassing something but I had preciously unloaded the form in fact then tried running of a different source workbook and it still kept the values. Your help has been invaluable thanks again.

Peter

 

[Mitchbvi]

As your last solution works think I will leave it as is. However I will remember the problem with Public variables, I had considered using an array with just the numbers but got stuck on working it out as it kept coming back to a user form to populate the array. Another useful tip you have taught me is setting the variable to Long. Thank you

 

[Yongle]

Glad you are up and running
Thanks for the feedback