Create Lists for Drop-Down Menus in Different Sheet

Question

I'm currently working with a workbook utilized at my work that has the following code. It currently takes about 10 seconds to complete, and needs to be executed several times throughout utilizing the workbook. Is there any way to speed up this process?

Right now, the user specifies enters the drop down items needed in Sheet2, the code creates an additional sheet named "Filter Builder", enters the data in a column, which is then copied into the corresponding cell in the Filter Options sheet, and finally pulled by Sheet1 to create a drop down list.

Sub Macro1()
'TURNS OFF THE SCREEN UPDATING SO IT BRING YOU TO THE FILTER OPTIONS TAB AFTER THE MACRO IS COMPLETE
Application.ScreenUpdating = False
'FILTERS OUT THE ROWS THAT DO NOT HAVE "SPECIAL VALIDATION" AND HIDES THEM
ActiveSheet.Range("$A1ドル:$AF5150ドル").AutoFilter Field:=5, Criteria1:="Special Validation"
'ADDS ANOTHER SHEET FOR THE DATA TO BE ENTERED INTO
Sheets.Add After:=Sheets(Sheets.Count)
'NAMES THE SHEET AS "FILTER BUILDER"
ActiveSheet.Name = "Filter Builder"
'PASTES THE DATA IN FILTER BUILDER THEN DELIMITS IT
Sheets("Checklist Builder").Columns("F:F").EntireColumn.Hidden = False
Sheets("Checklist Builder").Columns("F:F").Copy Sheets("Filter Builder").Columns("A:A")
Sheets("Checklist Builder").Columns("G:G").Copy Sheets("Filter Builder").Columns("B:B")
'DELIMITS THE DATA SO THE TEXT SPLIT UP IN TO DIFFERENT COLUMNS, DIVIDED UP BY COMMAS
Application.CutCopyMode = False
Columns("B:B").TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
 TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
 Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
 :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:= _
 True
'SELECTS THE DATA TO TRANSPOSE IT
Sheets("Filter Builder").Rows("2:1150").Select
'COPIES AND TRANSPOSES THE DATA IN THE "FILTER BUILDER" EXCEL SHEET TO "FILTER OPTIONS"
Application.CutCopyMode = False
Selection.Copy
 Sheets("Filter Options").Range("K1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
 :=False, Transpose:=True
'AUTO FITS THE COLUMNS IN THE EXCEL SHEET
Sheet3.Cells.EntireColumn.AutoFit
'TURNS OFF THE DISPLAY ALERT TO DELETE THE SHEET
Application.DisplayAlerts = False
'DELETES THE SHEET
Sheets("Filter Builder").Delete
'HIDES COLUMN 'F' IN THE CHECKLIST BUILDER AGAIN
Sheets("Checklist Builder").Columns("F:F").EntireColumn.Hidden = True
'TURNS THE DISPLAY ALERTS BACK ON
Application.DisplayAlerts = True
'TAKES AWAY THE AUTO FILTER THAT WAS APPLIED IN THE "CHECKLIST BUILDER" SHEET
Sheet2.Range("$A1ドル:$AF150ドル").AutoFilter Field:=5
'GOES BACK TO THE CHECKLIST BUILDER SHEET
Sheets("Checklist Builder").Select
Range("A1").Select
Application.ScreenUpdating = True
End Sub

• user109261: Can you provide sample data from Sheets("Filter Builder") before it is split?
• Kim: Yes, Definitely. The user would input something like "item1;item2;item3;" into Sheet2. Then the text "item1;item2;item3;" would be transposed to the Filter Builder, then the text would be split into cells in a column. For example, A1 would be item1, A2 would be item2, etc. Then, this information would be copied to the Filter Options Sheet. Upon the next iteration, the column to the right in Filter Options would populate, then Column C, etc. Is that helplful? @ThomasInzina

Answer 1

This will be hard to get right without the actual workbook.

• Stop adding and removing Sheets("Filter Builder"); just hide it.
• Avoid Select or Activate. Reference: Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset)
• By working with the data in arrays, you can avoid filtering & unfiltering you data as well as hiding and unhiding the columns.

• Use With block to qualify your ranges. Reference: Excel VBA Introduction Part 12 - With Statements

• Replace your static ranges ,e.g. .Range("$A1ドル:$AF5150ドル"), dynamic ranges ,e.g. .Range(.Range("A" & .Rows.Count).End(xlup),"AF1"))

Here are some inconsistencies

• The initial ActiveSheet, Sheet2 and Sheets("Checklist Builder") all appear to be the same Worksheet. Pick 1 and use it in a With block. (e.g. With Sheet2)
• Range("$A1ドル:$AF5150ドル").AutoFilter - .Range("$A1ドル:$AF150ドル").AutoFilter

---

This should work "As Is" but it is hard to say without the actual Workbook. You will have to add a Worksheet, name it "Filter Builder", hide it and forget about it. I would guess that it should take less than 1 sec to execute.

Sub Macro1()
'TURNS OFF THE SCREEN UPDATING SO IT BRING YOU TO THE FILTER OPTIONS TAB AFTER THE MACRO IS COMPLETE
 Application.ScreenUpdating = False
 Dim data As Variant, results As Variant
 Dim count As Long, x As Long
 With Sheets("Checklist Builder")
 data = .Range("$A1ドル:$AF5150ドル").Value
 ReDim results(1 To UBound(data), 1 To 2)
 'Fill array with the values
 For x = 1 To UBound(data)
 If count = 1 Or UCase(data(x, 7)) = "SPECIAL VALIDATION" Then
 count = count + 1
 results(count, 1) = data(x, 7)
 results(count, 2) = data(x, 8)
 End If
 Next
 End With
 'DELIMITS THE DATA SO THE TEXT SPLIT UP IN TO DIFFERENT COLUMNS, DIVIDED UP BY COMMAS
 With Sheets("Filter Builder")
 .Cells.Clear
 .Range("A1").Resize(UBound(results), UBound(results, 2)).Value = results
 .Columns("B:B").TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:=True
 .Rows("2:1150").Copy
 Sheets("Filter Options").Range("K1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
 :=False, Transpose:=True
 Application.CutCopyMode = False
 End With
 Application.ScreenUpdating = True
End Sub

• Kim: Thanks Thomas! This code copied "Special Validation" into Column A, and the checkpoint number into Column B, but the text used for the drop down was neither copied to the Filter Builder, nor to the drop-down in Sheet1.
• user109261: If you email me a mock workbook at [email protected], I will look at it.

Answer 2

After Thomas already talked about most of the changes that should be done to the code, I'd want to lose a few words on comments and abstraction.

All of the comments there restate what the code does in one way or the other. Redundancy may seem useful and people always say: comment your code, but I find comments that restate what the code does terrible.

If we remove these comments, we get to directly view what the code does. This also fixes the issue of stale comments. When a comment contradicts the code, who's correct? The code or the comment?

Turns out if you don't comment what the code does, then the code is always correct.

If you comment why the code does things, then the comment is usually correct, even when the code changes. It also eases understanding of the code, because it adds additional information, instead of redundant noise...