Filling an array with specific cells

[austin350s10]

I am trying to use the below code to fill a combobox with unique values. It works great except I can't figure out a way to replace BLOCK 1 with BLOCK 2. I want to use BLOCK 2 to populate mgNames so that only specific values are filled into mgNames. Currently BLOCK 1 just fills mgNames with an entire range without any conditions. Anyone have an idea how I can get BLOCK 2 to work in the way i'm intending it to?

Sub findNames()

Dim mgNames As Variant
Dim myCollection As New Collection
Dim temp As Variant

'so far I can get BLOCK 1 to fill mgNames with a range but I want to fill it with specific values with the condition in BLOCK 2.

'BLOCK 1 start ---------------------------
With ActiveSheet
    mgNames = Range(.Cells(2, 4), .Cells(.Rows.Count, 4).End(xlUp)).Value
End With
'BLOCK 1 end ----------------------------

'BLOCK 2 start ---------------------------
For Each c In Range(.Cells(2, 4), .Cells(.Rows.Count, 4).End(xlUp))
    If Trim(c.Offset(0, 7).Value) = "Hourly" Then
    
'looking for a way to fill mgNames with just the cells that meet the condition
    
    End If
Next c
'BLOCK 2 end ----------------------------

On Error Resume Next
For Each temp In mgNames
    myCollection.Add Item:=temp, Key:=temp
Next temp
On Error GoTo 0

ReDim mgNames(1 To myCollection.Count)
For temp = 1 To myCollection.Count
    mgNames(temp) = myCollection(temp)
Next temp

For temp = LBound(mgNames) To UBound(mgNames)
    boxNames.AddItem mgNames(temp)
Next temp

End Sub

 

[CodeNinja]

Create a variable for the range and fill it by unioning (is that a word?) the ranges based on condition.

Dim rng As Range
Dim c As Range
Set rng = Nothing

'On Error Resume Next

For Each c In Range(.Cells(2, 4), .Cells(.Rows.Count, 4))
    
    If Trim(c.Offset(0, 7).Value) = "Hourly" Then
        If rng Is Nothing Then
            Set rng = c
        Else
            Set rng = Application.Union(rng, c)
        End If
    End If
Next c
mgNames = rng

 

[austin350s10]

That seems to work for the most part. Only thing is the last line mgNames = rng. For some reason mgNames is only being filled with the first non-unioned range even though rng contains all of the correct ranges. I know thats kinda confusing so heres and example:

Say cells A1 & A2 meet the condition and are set to rng. Cells A3 & A4 do not meet the condition and are NOT set to rng. lastly, Cells A5 & A6 also meet the condition and are set to rng via a union.

So rng now equals $A$1:$A$2,$A$5:$A$6

When the code reaches mgNames = rng it only gets filled with the ranges A1 through A2 and skips the rest

So mgNames(1) equals the value from A1 and mgNames(2) equals the value from A2. However, It should look like;
mgNames(1) = A1, mgNames(2) = A2, mgNames(3) = A5, mgNames(4) = A6

I know this is confusing but if you have any insight I would appreciate it!

 

[CodeNinja]

Odd, I thought that would work... Perhaps you could just redim preserve and add the result right into the variant... or some variation of the following code that suits your needs...

Dim rng As Range
Dim c As Range
Set rng = Nothing
Dim mgNames As Variant
'On Error Resume Next
redim mgnames(1 to 1)

For Each c In Range(.Cells(2, 4), .Cells(.Rows.Count, 4))
    
    If Trim(c.Offset(0, 7).Value) = "Hourly" Then
        If rng Is Nothing Then
            Set rng = c
            mgNames(1) = c.Value
        Else
            ReDim preserve mgNames(1 To UBound(mgNames) + 1)
            mgNames(UBound(mgNames)) = c.Value
            
        End If
    End If
Next c

 

[Rick Rothstein]

I have a gut feeling there may be an easier way to do what you want to do but I am having trouble figuring out what that is. Forget the code you posted and forget the method you think you have to use to do what you want... just tell us what you have and what you ultimately want from it (for example, what is boxNames that you seem to be "filling"?). To do this, show us a sample of say 10 rows of data (between Columns B and D if I read your code correctly) and then show us what you want from that sample and how whatever it is you want from it ends up looking like.