Fix Excel VBA Macro Creating Combinations with Blank Values
Question details
The user needs to generate unique combinations of currencies and card types without including any blank values in the output.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Generating a Cartesian product from two data lists using a VBA macro.
- Observed behavior
- The current VBA macro successfully generates combinations but incorrectly includes rows containing blank currency values.
Before modifying your macro, make sure to save a backup of your current Excel workbook as a Macro-Enabled Workbook (.xlsm) to prevent any accidental data loss.
Use Scripting.Dictionary to Filter Blanks in VBA
This solution uses the Scripting.Dictionary object to build a unique list of non-blank values before calculating the combinations.
By leveraging dictionaries, you can automatically handle duplicates. Combined with a length check (Len and Trim), you ensure that only cells with actual text are processed into your final combinations.
Press ALT + F11 in your Excel workbook to open the Visual Basic for Applications (VBA) editor.
Click 'Insert' in the top menu and select 'Module' to create a new blank script window.
Copy and paste the following code. It uses Len(Trim(value)) > 0 to verify the cell isn't empty before adding it: Sub CreateCombinations() Dim currencies As Object, cardTypes As Object, c As Range, t As Range Dim currency, cardType, output(), i As Long Set currencies = CreateObject("Scripting.Dictionary") Set cardTypes = CreateObject("Scripting.Dictionary") currencies.CompareMode = vbTextCompare cardTypes.CompareMode = vbTextCompare With Sheets("Sheet1") For Each c In .Range("I2", .Cells(.Rows.Count, "I").End(xlUp)) If Len(Trim(c.Value)) > 0 Then currencies(c.Value) = c.Value Next c For Each t In .Range("K2", .Cells(.Rows.Count, "K").End(xlUp)) If Len(Trim(t.Value)) > 0 Then cardTypes(t.Value) = t.Value Next t End With ReDim output(1 To currencies.Count * cardTypes.Count, 1 To 2) For Each currency In currencies.Keys For Each cardType In cardTypes.Keys i = i + 1 output(i, 1) = currency output(i, 2) = cardType Next cardType Next currency With Sheets("Sheet2") .Columns("A:B").ClearContents .Range("A1:B1").Value = Array("Currency", "Card Type") If i > 0 Then .Range("A2").Resize(i, 2).Value = output End With End Sub
Press F5 or click the Run button to execute the code. Check 'Sheet2' to see the newly generated combinations without any blanks.
Use Built-in Excel Tools (Non-VBA Alternative)
If you are unfamiliar with VBA, you can filter blanks and generate combinations manually using Excel's data tools.
Seamlessly Run VBA Macros with WPS Spreadsheet
WPS Spreadsheet offers excellent compatibility with Microsoft Excel macros, allowing you to easily run VBA scripts that process data combinations, remove duplicates, and filter out blank values without changing your workflow.
- 1. Open Your File in WPS Spreadsheet: Launch WPS Office and open your .xlsm file containing the data.
- 2. Access the Developer Tab: Navigate to the top ribbon and click on the 'Developer' tab to access macro tools.
- 3. Open the VBA Editor: Click 'Visual Basic' or 'VBA Editor' to open the scripting environment.
- 4. Run the Script: Insert the dictionary script into a module and click Run to instantly generate your valid combinations.
Frequently Asked Questions
Why does my VBA macro read empty cells from the column?
Using .End(xlUp) to find the last row can sometimes capture cells containing empty strings (like those returned by formulas) or previously used cells. Adding a check like If Len(Trim(c.Value)) > 0 ensures that only cells with visible text are processed.
How does Scripting.Dictionary prevent duplicate combinations?
A Dictionary in VBA requires all keys to be unique. When you assign a value to a dictionary key that already exists, it simply overwrites it rather than adding a second instance. This automatically deduplicates your list before the combinations are generated.
Can I use Power Query instead of VBA for this task?
Yes. Power Query is a powerful alternative. You can load both lists into Power Query, filter out null or blank rows, add a custom column to generate the Cartesian product, and load the clean combinations back into your worksheet without writing any VBA code.
