vba - Remove selected numbers from a comma separated list management in Excel? -

this might little tricky, vba...

i have comma separated lists in cells based on start times on 5 minutes intervals need remove times 5 apart.

the numbers text, not time @ point. example, 1 list 2210, 2215, 2225, 2230, 2240 (the start times).

in case, 2215 , 2230 should removed need remove opposite numbers (i.e.,2210 , 2225) in other cases (the end times).

someone helped me specs:

a cell contains times: t(1), t(2), t(3), ... t(n). starting @ time t(1), each value in list examined. if t(x) less 6 minutes after t(x-1) delete t(x) , renumber t(x+1) t(n).

input:

2210, 2215, 2225, 2230, 2240 

output:

column1: 2210 column2: 2240 

this think require.

option explicit sub deleteselectedtimes()    dim rowcrnt long    rowcrnt = 2    while cells(rowcrnt, 1).value <> ""     cells(rowcrnt, 1).value = processsinglecell(cells(rowcrnt, 1).value, 1)     cells(rowcrnt, 2).value = processsinglecell(cells(rowcrnt, 2).value, -1)     rowcrnt = rowcrnt + 1   loop  end sub function processsinglecell(byval cellvalue string, byval stepfactor long) string    dim celllist() string   dim celllistcrntstg string   dim celllistcrntnum long   dim inxcrnt long   dim inxend long   dim inxstart long   dim timecrnt long    ' time in minutes   dim timelast long    ' time in minutes    celllist = split(cellvalue, ",")    if stepfactor = 1     inxstart = lbound(celllist)     inxend = ubound(celllist)   else     inxstart = ubound(celllist)     inxend = lbound(celllist)   end if    celllistcrntstg = trim(celllist(inxstart))   if (not isnumeric(celllistcrntstg)) or instr(celllistcrntstg, ".") <> 0     ' either sub-value not numeric or if contains decimal point     ' either way cannot time.     processsinglecell = cellvalue     exit function   end if   celllistcrntnum = val(celllistcrntstg)   if celllistcrntnum < 0 or celllistcrntnum > 2359     ' value not time formatted hhmm     processsinglecell = cellvalue     exit function   end if   timelast = 60 * (celllistcrntnum \ 100) + (celllistcrntnum mod 100)    inxcrnt = inxstart + stepfactor inxend step stepfactor     celllistcrntstg = trim(celllist(inxcrnt))     if (not isnumeric(celllistcrntstg)) or instr(celllistcrntstg, ".") <> 0       ' either sub-value not numeric or if contains decimal point       ' either way cannot time.       processsinglecell = cellvalue       exit function     end if     celllistcrntnum = val(celllistcrntstg)     if celllistcrntnum < 0 or celllistcrntnum > 2359       ' value not time formatted hhmm       processsinglecell = cellvalue       exit function     end if     timecrnt = 60 * (celllistcrntnum \ 100) + (celllistcrntnum mod 100)     if abs(timecrnt - timelast) < 6       ' delete unwanted time list       celllist(inxcrnt) = ""     else       ' current time becomes last time next loop       timelast = timecrnt     end if   next    cellvalue = join(celllist, ",")    if left(cellvalue, 1) = ","     cellvalue = mid(cellvalue, 2)     cellvalue = trim(cellvalue)   end if    while instr(cellvalue, ",,") <> 0     cellvalue = replace(cellvalue, ",,", ",")   loop    processsinglecell = cellvalue  end function 

explanation

sorry lack of instructions in first version. assumed question more technique manipulating data vba.

deleteselectedtimes operates on active worksheet. easy change work on specific worksheet or range of worksheets if require.

deleteselectedtimes ignores first row assume contains column headings. test worksheet has headings in row 1. processes columns , b of every row until reaches row empty column a.

processsinglecell has 2 parameters: string , direction. deleteselectedtimes uses direction values in column processed left right while values in column b processed right left.

i assume #value error because processsinglecell not check string of format "number,number,number". have changed processsinglecell if string not of format, change string.

i have no clear idea of or not know come more questions necessary.