excel - How to find the last cell in a column which is supposed to be blank but has spaces? -

so have data around 20,000 records. want set range such data row 2 20,000 checked in column a. however, cell 20,001 isn't blank, contain spaces well. (this data imported prior validation, cannot alter it)

when use .end(xlup) ends checking till 50,000th row.

any help?

sample:

column a

a

b

(2 spaces inserted)

---

i want check cells till b(including it)

update: managed return last required cell main sub

private sub last() dim rngx range  set rngx = activesheet.range("a1").entirecolumn.find("  ", lookat:=xlpart)      if not rngx nothing        /* return value      end if   end sub 

gd pnuts,

if want use vba, contemplate checking [space] character ? assuming cell contains spaces (or 1 matter)

something like:

dim r range set r = range("b")  each c in r.rows      if instr(1, c.value,chr(32)) > 0     'do     end if  next  

you function check of characters in cell.value string validate spaces ?

does ?