Removing Blanks from Dynamic Range

Question

I understand how to use the offset function for a dynamic range, but what if that dynamic range is within a specific number of additional columns? For example, say I have a worksheet with columns A:N, and the named range refers to D2:E2. If I add two more columns, that range should expand to D2:G2, but not include columns F and onward.

I'm currently using the offset function with the counta function to do this, but there are a number of natural blank cells within this range (because of merged cells). Is there a way for me to remove these blanks for use in the combobox's dropdown?

Currently I've defined the name as:

=OFFSET('Sheet 1'!$D$2,0,0,1,COUNTA('Sheet 1'!$D2:$ZZ2))

Which returns all of the values I'm looking for, but several blanks as well that I don't want in the dropdown.

I'm currently using the following code during the userform's initialization, but this doesn't seem to be working either:

Dim Rng As Range
Dim i As Long
Me.ComboBox1.RowSource = ""
    Set Rng = Range("Combo")
    For i = 1 To Rng.Rows.Count
        If Rng(i) <> "" Then
            Me.ComboBox1.AddItem Rng(i)
        End If
    Next i

I've also tried

Dim aCell As Range, ws1 As Worksheet, lastColumn As Long,  stopColumn As Long


Set ws1 = Worksheets("sheet 1")

With ws1
lastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
stopColumn = lastColumn - 12
Me.ComboBox1.RowSource = ""
With ws1
    For Each aCell In .Range("D2", .Cells(2, stopColumn))
        If aCell.Value <> "" Then
            Me.ComboBox1.AddItem aCell.Value
        End If
    Next
End With

Neither attempt has worked though, the combobox dropdown is empty.

Step through your code to see what's going on. In your second example you probably need lastColumn = .Cells(2, .Columns.Count).End(xlToLeft).Column because you might not have anything in row 1. — SJR
It should be the same as there are just as many values in row 1 as compared to 2, though I'll switch it to 2. It seems that there might actually be a deeper issue though. I added a MsgBox to the very beginning and it doesn't seem like the initialization is even being ran. The Userform's name is UserForm2 and the sub is Private Sub UserForm2_Initialize() - any idea why it would get skipped? — Valkeif
Ah, don't change the name of the procedure. It should be Private Sub UserForm_Initialize(). Right click on the user form in the VBE and choose View Code and choose it from the dropdown at the top. Or just delete the 2. — SJR
Ah I feel like a dummy now haha! Can't believe it was the procedure name that's had me hung up for the past hour. Was stepping through it with no problem because it was never actually activating. — Valkeif
If it's any consolation, it's not an uncommon thing to do. Going back to populating the combobox, a more efficient way might be to copy your range elsewhere, delete the blanks and then use the List method, which avoids the need to loop. Depends how many items you have I suppose. — SJR

Valkeif Valkeif · Accepted Answer · 2019-02-22T13:01:44

The second part of the code above actually was functional, I was just using the wrong procedure name. I was using UserForm2, and had renamed the initialize procedure Private Sub UserForm2_Initialize() when it should have instead been `Private Sub UserForm_Initialize()

Removing Blanks from Dynamic Range

1 Answers