Auto-filter to populate combo-box list always produces non-contiguous range (XL2007)

[AOB]

Hi all,

Have been frustrated by this wee pickle for several hours now, so hoping somebody on this ever excellent forum might be able to point out the error of my ways!...

I'm trying to use an auto-filter to provide either a range of cells or an array of strings (I don't care which) from which to populate a combobox ("cbACC") drop-down list.

The code filters column A, determined by a separate, independent combo-box ("cbPool") and should then add the filtered values from column F to the list for "cbACC".

The problem is, by using the SpecialCells function, the header row is always included in that filtered range. And - unless one happens to choose the first item on "cbPool" - that range will always be non-contiguous. Meaning I can't offset/resize the filtered range down to just those cells containing the required list items. (Data is suitably sorted so the only element introducing non-contiguity is the header row)

I've tried populating the list with a range :

With wsReference
[INDENT].AutoFilterMode = False
.Range(wsReference.Range("A1").CurrentRegion.Address).AutoFilter Field:=1, Criteria1:=cbPool.Value
[/INDENT][INDENT]Set rgACCs = .AutoFilter.Range.SpecialCells(xlCellTypeVisible).Offset(1, 5).Resize(.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Rows.Count - 1, 1)
 
[/INDENT]End With
 
cbACC.List = rgACCs.Value

And cell-by-cell (excluding the header cell which is "ACC"):

With wsReference
[INDENT].AutoFilterMode = False
.Range(wsReference.Range("A1").CurrentRegion.Address).AutoFilter Field:=1, Criteria1:=cbPool.Value
 
For Each Cell In .AutoFilter.Range.SpecialCells(xlCellTypeVisible).Offset(, 5).Resize(, 1)
[INDENT]If Cell.Value <> "ACC" Then
[INDENT]cbACC.AddItem Cell.Value
[/INDENT]End If
[/INDENT]Next Cell
[/INDENT]End With

But I can't define the appropriate range without including the header row, which means I can't have a contiguous range, which means my offsetting/resizing fails miserably.

Any suggestions would be very gratefully received!!

Thanks in advance

AOB

 

[AOB]

Hi,

I've thrown together a very inefficient workaround which removes the need to offset & resize and instead loops through all cells in the filtered range (but only taking the values from column F)

With wsReference
[INDENT].AutoFilterMode = False
.Range(wsReference.Range("A1").CurrentRegion.Address).AutoFilter Field:=1, Criteria1:=cbBalancePool.Value
 
For Each Cell In .AutoFilter.Range.SpecialCells(xlCellTypeVisible)
[INDENT]If Cell.Value <> "DACC" [COLOR=red][B]And Cell.Column = 6[/B][/COLOR] Then
[INDENT]cbDACC.AddItem Cell.Value
[/INDENT]End If
[/INDENT]Next Cell
 
[/INDENT]End With

This seems to do the trick but I'm not particularly fond of it as it multiplies the loop iterations by a factor of 6. Not a problem if the filter only returns a handful of results but this escalates the larger the set of filtered results.

So, while I do have a basic solution, I'd still be curious to know of a 'cleaner' way of removing the header from that filtered range, if one exists...

Thanks anyway though!

AOB

 

[Andrew Poulsom]

Try offsetting and resizing first:

With .AutoFilter.Range.Columns(6)
    For Each Cell In .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        If Cell.Value <> "ACC" Then
            cbACC.AddItem Cell.Value
        End If
    Next Cell
End With

 

[AOB]

Or better still (unless you see any potential issues with this?...), define the range, remove the need for any For/Next loop and just define the list items by the range itself :

With .AutoFilter.Range.Columns(6)
[INDENT]Set rgACCs = .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
 
[/INDENT]End With
 
cbACC.List = rgACCs.Value

Thanks a million Andrew, I'd never have thought of doing the offset/resize prior to examining the visible cells - made total sense once you pointed that out.

Really appreciate you taking the time to have a look.

AOB