OT Excel help

Discussion in 'CycleChat Cafe' started by lifeson, 13 Sep 2007.

  1. lifeson

    lifeson New Member

    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"?
  2. bonj2

    bonj2 Guest

    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...
  3. OP

    lifeson New Member

    Thanks Bonj
    Thats worked a treat :smile:
  4. OP

    lifeson New Member

    Next question: :smile:
    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 :ohmy:

    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)
    This does not seem to work I was expecting to return the value in column A
    Dismiss Notice