Load a column into an array/listbox, skipping a certain string 3

[davedave24]

Hi all. I have a column with many different entries. It's set out weekly, so for each week there will be column headings. How would I load the contents of the column into a listbox, while skipping the empty cells and the column heads?

Example: the string to skip (the heading) is "Consignment Note"

C1 = "Consignment Note"
C2:C10 = stuff we want
*blank cells*
C14 = "Consignment Note"
C15:C26 = stuff we want
*blank cells*
C30 = "Cosignment Note"
C31:C35 = stuff we want
*blank cells
C39 = "Consignment Note"
etc.
etc.

The 'consignment note' string will not appear in any of the data we want by chance; it's all random numbers/letters.

Thanks in advance guys.

 

[SkipVought]

hi,

loop thru the list excluding blanks & "Consignment Note"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[davedave24]

I solved it thanks

Private Sub comboCustomers_Change()
        On Error Resume Next
        Dim NoteArray As Variant
        Workbooks("All Deliveries " & Year(Date) & ".xlsx").Activate
        NoteArray = Workbooks("All Deliveries 2011.xlsx").Sheets(comboCustomers.Text).Range("C:C").Value
        Dim i As Long
        ListBox1.Clear
        
        For i = LBound(NoteArray) To UBound(NoteArray)
            If NoteArray(i, 1) <> vbNullString And NoteArray(i, 1) <> "Consignment Note" Then
                ListBox1.AddItem (NoteArray(i, 1))
            End If
        Next i
End Sub

 

[GlennUK]

Have you had a look at UBound(NoteArray) when your code is running? Just curious as to how mnay times through that loop you are interating.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[davedave24]

I'd imagine it's extremely innefficient Glenn; I did a msgbox of it and it said something like 1053373

 

[Gruuuu]

davedave, something that may save you a little bit of grief, and all around a little better coding practice.

You can find the last used cell in a column by using the Range().Find method. You can specify that it search backwards, and it returns a cell object. I don't have exact syntax at the moment, but it's in the VBA help file.

 

[GlennUK]

This will loop a shorter range:

Private Sub comboCustomers_Change()
        On Error Resume Next
        Dim NoteArray As Variant
        Workbooks("All Deliveries " & Year(Date) & ".xlsx").Activate
        ListBox1.Clear

        For Each c in InterSect(Sheets(comboCustomers.Text).Range("C:C").Value, Sheets(comboCustomers.Text).UsedRange)
               
            If c.Value <> vbNullString And c.Value <> "Consignment Note" Then
                ListBox1.AddItem c.Value
            End If
        Next i
End Sub

... not tested.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[GlennUK]

The "Next i" should just be "Next".

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[davedave24]

Thanks Glenn - what is the variable 'c' in this code?

 

[PHV]

what is the variable 'c' in this code?
A Range object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[davedave24]

Sorry it's not working, have I copied it over wrong?

 

[combo]

Intersect has ranges as arguments:

For Each c in InterSect(Sheets(comboCustomers.Text).Range("C:C")[s].Value[/s], Sheets(comboCustomers.Text).UsedRange)

combo

 

[davedave24]

Ah thanks combo, works great now.

Thanks for your help everyone. Stars all round.

 

[GlennUK]

Thanks combo ... I obviously didn't proof-read the code enough. :-D

Cheers, Glenn.

Beauty is in the eye of the beerholder.