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

 

[rlv01]

Something like this might work to dynamically build the range via union. Not sure about the copy statement though, if the columns are non-contiguous.

Sub TestForm()
    Dim sSheet As String
    Dim URange As Range
    Dim WS As Worksheet
    Dim ColArr As Variant
    Dim SheetCol As Variant

    ColArr = Array(WellNameCol, ProdDatecol, ProdTypeCol, ProdPriceCol, GrossCol, ExpNameCol, ExpAmountCol, OwnNetCol)

    Unload UserForm1
    sSheet = ActiveSheet.Name

    ActiveWorkbook.Sheets.Add
    ActiveSheet.Name = "ActiveColumns"

    With Worksheets(sSheet)
        For Each SheetCol In ColArr
            If Not .Columns(SheetCol) Is Nothing Then
                If URange Is Nothing Then
                    Set URange = .Columns(SheetCol)
                Else
                    Set URange = Application.Union(URange, .Columns(SheetCol))
                End If
            End If
        Next SheetCol
        Debug.Print URange.Address

        URange.Copy Worksheets("Activecolumns").Range("A1")
    End With
End Sub

 

[Yongle]

Very similar to @rlv01

Sub TestForm()
    Dim c As Variant, Rng As Range
    Unload UserForm1

    With ActiveSheet
        For Each c In Array(WellNameCol, ProdDatecol, ProdTypeCol, ProdPriceCol, GrossCol, ExpNameCol, ExpAmountCol, OwnNetCol)
            If c > 0 Then
                If Rng Is Nothing Then Set Rng = .Columns(c) Else Set Rng = Union(Rng, .Columns(c))
            End If
        Next c
    End With

    With ActiveWorkbook.Sheets.Add
        .Name = "ActiveColumns"
        Rng.Copy .Range("A1")
    End With

End Sub

 

[Norie]

Peter

How are you getting the user to select the columns?

Also, how are you populating the column variables, e.g. WellNameCol?

 

[Mitchbvi]

Peter

How are you getting the user to select the columns?

Also, how are you populating the column variables, e.g. WellNameCol?

A user form provides the column numbers like this

Private Sub WellName_Change(0
WellNameCol=Range(UserForm1.WellName.Value).Column
ProdDate.SetFocus
End Sub

thanks for getting back to me

 

[Mitchbvi]

Thanks very much I will try that. My original code works on non contiguous columns it only fails if all the columns are not present. Had thought an array may work but could not figure how.

peter

 

[Mitchbvi]

Thanks will try

 

[Mitchbvi]

Just wanted to let you know works perfectly thank you very much.

 

[Mitchbvi]

Very similar to @rlv01

Sub TestForm()
    Dim c As Variant, Rng As Range
    Unload UserForm1

    With ActiveSheet
        For Each c In Array(WellNameCol, ProdDatecol, ProdTypeCol, ProdPriceCol, GrossCol, ExpNameCol, ExpAmountCol, OwnNetCol)
            If c > 0 Then
                If Rng Is Nothing Then Set Rng = .Columns(c) Else Set Rng = Union(Rng, .Columns(c))
            End If
        Next c
    End With

    With ActiveWorkbook.Sheets.Add
        .Name = "ActiveColumns"
        Rng.Copy .Range("A1")
    End With

End Sub

Hope I am not breaking forum rules but I have a question based on your solution. I find that if I run the routine a second time with a different selection it includes what ever had been selected before. Basic problem is I cannot clear the existing RefEdit controls. I have tried setting the values to null in the routine that opens the user form, with a before and after MsgBox to make sure they were null and that did not work. I have to exit Excel to get it to work.

Thanks

 

[Yongle]

The usefulness of Public variables is that unless cleared they retain their values
- but that can also be the problem with them!
- in this instance they require clearing EVERY time before running the routine again

The routine works without error for me and this is what I did
- reset the values to ZERO (not Null) when userform is initialized
- code used is below
- all procedures located in the UserForm code module
- public variables declared in a Standard module
- date & time suffix added to sheet name to ensure sheet name unique during testing
- Debug.Print added to Reset procedure so that you can see the values in the Immediate Window ( {CTRL} G (when in VBA edit) displays that window)

If you are wanting to run different scenarios without unloading the userform, then column variables must be reset before running each scenario

Private Sub UserForm_Initialize()
    Reset
End Sub

Private Sub Reset()
    Debug.Print "BEFORE", WellNameCol, ProdDatecol, ProdTypeCol, ProdPriceCol, GrossCol, ExpNameCol, ExpAmountCol, OwnNetCol
    WellNameCol = 0
    ProdDatecol = 0
    ProdTypeCol = 0
    ProdPriceCol = 0
    GrossCol = 0
    ExpNameCol = 0
    ExpAmountCol = 0
    OwnNetCol = 0
    Debug.Print "AFTER", WellNameCol, ProdDatecol, ProdTypeCol, ProdPriceCol, GrossCol, ExpNameCol, ExpAmountCol, OwnNetCol
End Sub

Private Sub CommandButton3_Click()
    Dim c As Variant, Rng As Range
    Unload UserForm1
    
    With ActiveSheet
        For Each c In Array(WellNameCol, ProdDatecol, ProdTypeCol, ProdPriceCol, GrossCol, ExpNameCol, ExpAmountCol, OwnNetCol)
            If c > 0 Then
                If Rng Is Nothing Then Set Rng = .Columns(c) Else Set Rng = Union(Rng, .Columns(c))
            End If
        Next c
    End With
    With ActiveWorkbook.Sheets.Add
        .Name = "ActiveColumns" & Format(Now, "yymmddhhmmss")
        Rng.Copy .Range("A1")
    End With
End Sub

For testing I used 2 command buttons on the UserForm to vary columns selected

Private Sub CommandButton1_Click()
    WellNameCol = 2: ProdDatecol = 3: ProdTypeCol = 4: ProdPriceCol = 7: ExpNameCol = 10
End Sub

Private Sub CommandButton2_Click()
    WellNameCol = 4: ProdDatecol = 8: ProdTypeCol = 12: ProdPriceCol = 16: GrossCol = 25
End Sub

As an aside, consider declaring your column variables as Long (can also use Integer) thus matching what they are expected to contain

Public  WellNameCol As Long, ProdDatecol As Long, ProdTypeCol As Long,  ProdPriceCol As Long, GrossCol As Long, ExpNameCol As Long, ExpAmountCol  As Long, OwnNetCol As Long