excel vba - Populating combobox with dynamic horizontal range -

i’m needing vba code value column ‘a’ , list adjacent horizontal non blank cells right in userform combox1.

sample data:

+-----+----+----+----+---+ |   | b  | c  | d  | e | +-----+----+----+----+---+ |  a1 | 63 |    | 55 | 5 | +-----+----+----+----+---+ 

sudo code:

sub test() myval = “a1” findme = myval set match = sheets(“sheets1”).range(a:a).find(findme) myrange = foundrange userform1.combobox.value = myrange exit sub 

in example code above, foundrange row value found, plus columns ‘b’ through ‘e’, minus blanks.

combobox values:

63 55 56

thank you!

create userform named userform1, combobox named combobox , button named commandbutton1. on image below:

then use code on commandbutton populate combobox:

private sub commandbutton1_click()     dim ws1 worksheet     dim long, c long     dim rng range, rng2 range     dim cellfound range     dim lastrow long, lastcolumn long  set ws1 = thisworkbook.sheets(1) findme = "a1" lastrow = ws1.cells(ws1.rows.count, "a").end(xlup).row set rng = ws1.range("a:a") 'range search set rng2 = rng(lastrow, 1)          rng          set cellfound = .find(what:=findme, after:=rng2, lookin:=xlvalues)             if not cellfound nothing                 firstaddress = cellfound.address                                     lastcolumn = ws1.cells(ws1.range(cellfound.address).row, ws1.columns.count).end(xltoleft).column                     = range(cellfound.address).column + 1 lastcolumn                     if ws1.cells(range(cellfound.address).row, i) <> "" combobox.additem ws1.cells(range(cellfound.address).row, i).value                     next                     set cellfound = .findnext(cellfound)                 loop while not cellfound nothing , cellfound.address <> firstaddress              end if         end  end sub 

this code uses .find function wanted use add item on combobox combobox.additem. note code inside userform, otherwise changes necessary turn global.

after finding a1 use address row obtain last column. , loop through cellfound + 1column (2 on sample) last column comparing if value in different blank. if different, add combobox.

lastcolumn = ws1.cells(ws1.range(cellfound.address).row, ws1.columns.count).end(xltoleft).column = range(cellfound.address).column + 1 lastcolumn  if ws1.cells(range(cellfound.address).row, i) <> "" combobox.additem ws1.cells(range(cellfound.address).row, i).value next 

then find next value , make same thing, column can have more 1 match.

set cellfound = .findnext(cellfound) 

the findme value can replaced value, such textboxes or cell.values.

and range search rng can set range using. e.g.: entire worksheet