1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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
    OP
    lifeson

    lifeson New Member

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

    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)
    =vlookup(sDesc,data,1,FALSE)
    This does not seem to work I was expecting to return the value in column A