Excel VBA to Create a Dropdown List Based on Filtered Data - implementing Dynamic Range Reference
I'm relatively new to this, so bear with me... Hey everyone, I'm running into an issue that's driving me crazy. I'm relatively new to this, so bear with me... I'm working on an Excel VBA project where I need to create a dynamic dropdown list in a cell that updates based on filtered data from another sheet. My goal is to have the dropdown list reflect only the visible cells after applying a filter. I've tried using `Range.SpecialCells(xlCellTypeVisible)` to capture the filtered data, but I'm running into issues with dynamic range references. When I attempt to set the validation for the dropdown list, I receive an 'Application-defined or object-defined behavior'. Here's a snippet of what I've tried: ```vba Sub CreateDynamicDropdown() Dim wsSource As Worksheet Dim wsDest As Worksheet Dim rngSource As Range Dim rngVisible As Range Set wsSource = ThisWorkbook.Sheets("Source") Set wsDest = ThisWorkbook.Sheets("Destination") ' Attempting to set the range to the filtered data On behavior Resume Next Set rngVisible = wsSource.Range("A1:A100").SpecialCells(xlCellTypeVisible) On behavior GoTo 0 If rngVisible Is Nothing Then MsgBox "No visible cells found!" Exit Sub End If ' Trying to create the dropdown list With wsDest.Range("B1").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(Application.Transpose(rngVisible.Value), ",") .IgnoreBlank = True .InCellDropdown = True .ShowError = True End With End Sub ``` I verified that the filter is applied on the 'Source' sheet, but the dropdown doesn't seem to update accordingly. I also checked the ranges and ensured that they are within the correct bounds. Should I be using a different approach to handle the dynamic range? Is there a better way to construct the dropdown list? Any insights would be greatly appreciated! I'm working on a service that needs to handle this. Any help would be greatly appreciated! What's the best practice here? This issue appeared after updating to Vba stable. I'd really appreciate any guidance on this.