How do I select the number of rows until i reach a certain value

[asddsaasddas]

For instance, I have column A populated with different random words. So for example at row 6, I have a "#N/A" instead of a random word. I want to be able to select all the values above row 6. Technically the "#N/A" is like a stopper to prevent the system from selecting it or anything below it. Is there any way to select all the values above the "#N/A". The number of random words are not definite. Thus, I may have #N/A" to appear at row 13 and the values from row 1 to 12 can be selected. I want the code to be flexible and can adapt to "#N/A" in any row as long as it is in column A.

 

[asddsaasddas]

The other macro must be getting the cell value from somewhere. Check that first.

Then try : =(cell ref)=0
If this formula returns FALSE, try : =TRIM(cell ref)=0
If this returns FALSE, try : =INT(cell ref)=(cell ref). This is to check the value is a whole number
To confirm the cell only contains one character : =LEN(cell ref)

Do I not have to -1 from count or something?? Does the code provided previously helps to count the number of rows and subtract 1 from the total amount of rows?
No. The code sets the range from the startcell to the cell immediately above the last data cell - which I believe is what you want.
Why don't you try it?

Hi, I've tried all 4 of the formulas given and they return the values "TRUE", "FALSE", "TRUE" and "1" respectively. Also I've tried the code provided and it works . Thanks. Was wondering why the second formula return "FALSE"

"=INDEX($AA$2:$AA168,MATCH(0,INDEX(COUNTIF($AE$4:AE24,$AA$2:$AA$168),0,0),0))" This is the formula in that particular cell

 

[footoo]

Was wondering why the second formula return "FALSE"

The cell must be formatted as Text.

 

[asddsaasddas]

Was wondering why the second formula return "FALSE"

The cell must be formatted as Text.

"=INDEX($AA$2:$AA168,MATCH(0,INDEX(COUNTIF($AE$4:AE24,$AA$2:$AA$168),0,0),0))" This is the formula in that particular cell