OT Excel help

[lifeson]

Another excel question for the experts
I use this code to create a list of values to use as a rowsource for a combo box:

dim MakeList as Range
Set MakeList = Range(("E1"), Selection.End(xlDown)).SpecialCells(xlCellTypeVisible)

the list is the result of a filtered list using autofilter (hence the xlcelltypeVisible)

How do I make the range "All the visible cells in column E except the first row"?

 

[bonj2]

firstly don't use 'Selection' in code unless you're really sure that you know you want to - i.e. it definitely depends on what the currently selected cell is.

something like
dim MakeList as Range
Set MakeList = Range(("E2"), Range("E2").End(xlDown)).SpecialCells(xlCellTypeVisible)

I would personally prefer

dim MakeList as Range
Set MakeList = Range(cells(2,5),cells(2,5).end(xldown)).SpecialCells(xlCellTypeVisible)

but specifying cells by string is perfectly ok


also be aware that if you have a gap in the data (i.e. a null value in one of the cells in column E) then 'End' will only go down as far as that...

 

[lifeson]

Thanks Bonj
Thats worked a treat

 

[lifeson]

Next question:
how do i return the value of a cell in column A if I know the value in column E for the same row
I am familiar with vlookup and can get it working from left to right, but I cant get it to work for right to left

ie in column A i have a code BLR2500 and in column E is the description
I have a named range "data"
this works ok:
=vlookup("BLR2500",data,5,FALSE) and returns the description value in column E

Now I have the description and want to return the code
description is declared as a variable (sDesc)
=vlookup(sDesc,data,1,FALSE)
This does not seem to work I was expecting to return the value in column A