VBA Userform Checkboxes to Find & Isolate Dynamic Columns

[verticalstall]

Hey all!

So I'm definitely under water here...

I have a worksheet with 240 columns, each with a header. I have checkboxes on a userform that reference up to 54 of these columns. Users can select checkboxes based on what they would like to see in the final data set (typically 25-30 boxes will be checked). I need to either copy the relevant columns to a new worksheet (my current method) or delete all unselected columns (perhaps this would be faster/more efficient?). I then need to rearrange the columns to a pre-defined and constant order (currently using a LBound/UBound array function).

My current code extracts these columns to a new worksheet based on their static position within the original dataset using an IF…THEN statement evaluating each checkbox (which I'm sure is far from the most efficient method). However, I would like to create code that can find the relevant columns if they were to shift around (addition of new columns, etc.) in future datasets.

My thought is to use the checkboxes to pass the column header titles to an array. With the user-defined "relevant" columns, I could then use that array to search through the 240 columns for each header title in the array. Any unselected columns would be placed in a union for deletion at the conclusion of the search. The roadblock: I'm still really new at VBA and I'm having trouble translating the ideas (are they even correct?) into code. Any shoves in the right direction would be appreciated!

 

[DushiPunda]

The easiest way I've found to keep track of data (in your case columns), is to use named ranges. I'm not sure what your your current code looks like, but if you named each of the headers of the columns you're wanting to keep track of like so:

(Assuming your headers are in row 1)
A1 = Example1
B1 = Example2
C1 = Example3

Then, in your code, instead of referring to the checkbox that references column A, for instance like this:
Worksheets("Sheet1").Range("A:A")
or
Worksheets("Sheet1").Columns(1)

You can then do this:

Worksheets("Sheet1").Range("Example1").EntireColumn

You can even more simply just select the entire range in each column that you want to copy and name that. If rows are added inside of the range you've named, the range of the named range will automatically expand. For example:

1. A1:A25 is named Example1
2. You insert a new row in between A12 and A13
3. A1:A26 is now the range of Example1

If you add a row to the end of your named range, then it won't automatically update, but there are ways to create "dynamic named ranges".

Hopefully this sets you off in a good direction.

To name a range, simply select a cell (or multiple cells), then:

1. Type the name in the box that is right above Column A, to the left of the formula bar.

or

2. Go to the Formulas tab, click Name Manager, click New, type the name you want, change the scope if you want to (I prefer to use worksheet-scoped named ranges), you should have already selected the range so it should automatically be in the "Refers to" box, then click OK.

 

[verticalstall]

DushiPunda,

Thanks for the suggestion. Named ranges would definitely make tracking the columns easier. The issue is that the dataset is being loaded into my macro-enabled workbook (“HH”) from a separate workbook (“Input”) that users obtain from an online program. The ranges (specifically, number of rows) in "Input" will always be different for each user. And named ranges will be static, correct? Ideally, I would like to first find the relevant headers and reference the column in any code. This would be in case future iterations of “Input” have new columns inserted or change the column order (e.g. creating a situation where "Header1" was originally in C1 but now is E1). My current code finds these columns in a static manner. A partial example:

    If CheckBoxAdeno.value = True Then
        Sheets("MRRS Input2").Range("AE1:AE" & lastRow).value = Sheets("MRRS Input").Range("HE1:HE" & lastRow).value    'Adenovirus Due
    Else: End If
    If CheckBoxAnthrax.value = True Then
        Sheets("MRRS Input2").Range("Z1:Z" & lastRow).value = Sheets("MRRS Input").Range("GB1:GB" & lastRow).value      'Anthrax Due
    Else: End If
    If CheckBoxCholera.value = True Then
        Sheets("MRRS Input2").Range("Q1:Q" & lastRow).value = Sheets("MRRS Input").Range("DW1:DW" & lastRow).value      'Cholera Due
    Else: End If
    If CheckBoxHepA.value = True Then
        Sheets("MRRS Input2").Range("N1:N" & lastRow).value = Sheets("MRRS Input").Range("DC1:DC" & lastRow).value      'HepA Due
    Else: End If

These iterations continue for each of the 54 checkboxes that then refer to the (currently) static column reference. So I guess my first question: is there a more code-efficient way to assess checkbox values, then (if true) find the relevant heading, and finally either copy to a new sheet or delete all unselected columns?

 

[Norie]

Why not list all the column headers in a listbox?

That would be straightforward to populate and could be set up show a checkbox to each header in the list.

When it comes to copying the columns you can just loop through the listbox to see what the user has selected.

 

[verticalstall]

Norie,

Thanks for the listbox suggestion - that will be much easier to populate. I'll do some editing on the userform and (inevitably) come back with some more questions. Thanks again!

 

[DushiPunda]

DushiPunda,

Thanks for the suggestion. Named ranges would definitely make tracking the columns easier. The issue is that the dataset is being loaded into my macro-enabled workbook (“HH”) from a separate workbook (“Input”) that users obtain from an online program. The ranges (specifically, number of rows) in "Input" will always be different for each user. And named ranges will be static, correct? Ideally, I would like to first find the relevant headers and reference the column in any code. This would be in case future iterations of “Input” have new columns inserted or change the column order (e.g. creating a situation where "Header1" was originally in C1 but now is E1). My current code finds these columns in a static manner. A partial example:

    If CheckBoxAdeno.value = True Then
        Sheets("MRRS Input2").Range("AE1:AE" & lastRow).value = Sheets("MRRS Input").Range("HE1:HE" & lastRow).value    'Adenovirus Due
    Else: End If
    If CheckBoxAnthrax.value = True Then
        Sheets("MRRS Input2").Range("Z1:Z" & lastRow).value = Sheets("MRRS Input").Range("GB1:GB" & lastRow).value      'Anthrax Due
    Else: End If
    If CheckBoxCholera.value = True Then
        Sheets("MRRS Input2").Range("Q1:Q" & lastRow).value = Sheets("MRRS Input").Range("DW1:DW" & lastRow).value      'Cholera Due
    Else: End If
    If CheckBoxHepA.value = True Then
        Sheets("MRRS Input2").Range("N1:N" & lastRow).value = Sheets("MRRS Input").Range("DC1:DC" & lastRow).value      'HepA Due
    Else: End If

These iterations continue for each of the 54 checkboxes that then refer to the (currently) static column reference. So I guess my first question: is there a more code-efficient way to assess checkbox values, then (if true) find the relevant heading, and finally either copy to a new sheet or delete all unselected columns?

Named ranges aren't static...if you name B1 "test", then insert a row in between Column A & B, then C1 will then be named test. So naming them will give you the ability to refer to them no matter where they are, even if columns have been inserted.

- Looking at your code. If you have cells HE1, GB1, DW1, DC1 named on "MRRS Input", then you can refer to your range in this fashion:

Sheets("MRRS Input").Range(Sheets("MRRS Input").Cells(1, Sheets("MRRS Input").Range("Your_HE1_Name").Column), Sheets("MRRS Input").Cells(lastRow, Sheets("MRRS Input").Range("Your_HE1_Name").Column)).Value

Which can be drastically shortened like so:

With Sheets("MRRS Input")
    .Range(.Cells(1, .Range("Your_HE1_Name").Column), .Cells(lastRow, .Range("Your_HE1_Name").Column)).Value
End With

- According to the code you've provided, you're pulling data from "MRRS Input" and inserting it into "MRRS Input2"...but there's no mention of the HH workbook...Unless you meant, because the Input workbook (workbooks?) are obtained from an online program, naming the column headers isn't possible as it's not your product?

-If that is the case, your best bet is to probably use the Range.Find method to find the column you're looking for. Assuming the following is true for column headers:
1) they exist
2) they are in Row 1
3) they are unique
4) they won't change (value)

You can do something like this:

Worksheets("Sheet1").Range("1:1").Find(What:="Column Header Text", LookIn:=xlValues, _
                                    LookAt:=xlWhole, SearchOrder:=xlByColumns, _
                                    SearchDirection:=xlNext, MatchCase:=False).Column

Would probably be best if you assigned the result to a variable (integer or long), and from there you can refer to the range you want in the fashion I outlined above, but replacing ".Range("Your_HE1_Name").Column" with the name of your variable.

Obviously, you'll have to repeat this for as many columns as you need to (in your case 54 columns) and it will make your procedure much longer, but the plus side is you won't have any hard-coded ranges.

 

[verticalstall]

- According to the code you've provided, you're pulling data from "MRRS Input" and inserting it into "MRRS Input2"...but there's no mention of the HH workbook...Unless you meant, because the Input workbook (workbooks?) are obtained from an online program, naming the column headers isn't possible as it's not your product?

That is correct. Many other users will be using this program and downloading the "Input Workbook" from the online program, hence I won't be able to change the column headers. Relevant data from the "Input Workbook" is copied into the "HH Workbook," wherein all this code resides.

In any case, I took Norie's advice and transitioned over to Listboxes. The following code loops through each of 7 Listboxes on my userform. It passes any selected values to row 1 on "Sheet2." Of note, I needed to use different (cleaner, more user-friendly) values for the Listboxes, so the "Sheet1" reference is to grab the actual values and pass them to "Sheet2."

Private Sub CommandButton1_Click() 'Pass all selected listbox values to Sheet2
    Dim InputCol As Long, InputIndex As Long, NR As Long
    Dim lb As Control
    InputCol = 1


    With lb
        For i = 1 To 7
            Set lb = Me.Controls("ListBox" & i)
            For InputIndex = 0 To lb.ListCount - 1
                If lb.Selected(InputIndex) Then
                    Sheets("Sheet2").Cells(1, InputCol) = Sheets("Sheet1").Range("Input" & i).Cells(InputIndex + 1)
                    InputCol = InputCol + 1
                End If
            Next
        Next i
    End With
    Unload Me
End Sub

So now that I have the user-defined Listbox values in the header row of "Sheet2," my idea was to loop through each, find the corresponding header value in "MRRS Input" (this sheet holds the copied data from the "Input Workbook"), and finally copy that data to "Sheet2." This should extract only the relevant columns and put them in a standardized order on "Sheet2." I've come up with the following code to accomplish that task:

Sub test()
    Dim wsM As Worksheet, ws2 As Worksheet
    Dim LastColM As Long, LastCol2 As Long, LastRowM As Long, RefHead As Long
    Dim rFind As Range
    Set wsM = Sheets("MRRS Input")
    Set ws2 = Sheets("Sheet2")
    LastColM = wsM.Cells(1, Columns.count).End(xlToLeft).Column
    LastRowM = wsM.Cells(Rows.count, 9).End(xlUp).row
    LastCol2 = ws2.Cells(1, Columns.count).End(xlToLeft).Column
    
    For RefHead = 1 To LastCol2
        Set rFind = Range(Cells(1, 1), Cells(1, LastColM)).Find(What:=ws2.Cells(1, RefHead), _
            After:=Cells(1, 21), _
            LookAt:=xlWhole, _
            LookIn:=xlValues, _
            SearchOrder:=xlByColumns, _
            SearchDirection:=xlNext, _
            MatchCase:=True, _
            SearchFormat:=False)
        ws2.Range(Cells(2, RefHead), Cells(LastRowM, RefHead)).value = _
        wsM.Range(Cells(2, rFind.Column), Cells(LastRowM, rFind.Column)).value
    Next RefHead
End Sub

Unfortunately, I'm getting Run-time error '1004': Method of 'Range' of object '_Worksheet' failed with this line highlighted:

        ws2.Range(Cells(2, RefHead), Cells(LastRowM, RefHead)).value = _
        wsM.Range(Cells(2, rFind.Column), Cells(LastRowM, rFind.Column)).value

Any help would be appreciated, and thanks for everything so far!!!