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.

 

[footoo]

H

Hi, is working now thanks. May I ask if "#N/A" was replaced by a number will it still work. Tried it at my end and it doesn't.

Post the code you tried.
What sort of data is in the column?

 

[asddsaasddas]

Post the code you tried.
What sort of data is in the column?

Sub setnameforrange2()
    
'declare variables
Dim startCell2 As Range
Dim lastRow2 As Long
Dim ws2 As Worksheet

'set objects
Set ws2 = ThisWorkbook.Worksheets("Sheet19")
Set startCell2 = Range("AE5")



'Find last row of data
lastRow2 = ws2.Cells(ws2.Rows.Count, startCell2.Column).End(xlUp).Row




On Error Resume Next
Set Rng = Range(startCell2, Columns(startCell2.Column).Find(0)(0))
If Rng Is Nothing Then Set Rng = Range(startCell2, Cells(lastRow2, startCell2.Column))
On Error GoTo 0
Rng.Select
Rng.Name = "testdata2"

This is the code that i tried, seem to work with String but not numbers

 

[footoo]

In what way was it not working?

Try this :

Set Rng = Range(startCell2, Columns(startCell2.Column).Find(0, lookat:=xlWhole)(0))

 

[asddsaasddas]

In what way was it not working?

Try this :

Set Rng = Range(startCell2, Columns(startCell2.Column).Find(0, lookat:=xlWhole)(0))

Not working as in, it does not search for a "0" and take the rows above it. Instead, it selects the whole row based on the number of rows with values in it.

'declare variables
Dim startCell As Range
Dim Lastrow As Long
Dim ws As Worksheet
Dim FinallastRow As Range

'set objects
Set ws = ThisWorkbook.Worksheets("zzMAIN")
Set startCell = Range("AE5")


'Find last row of data
Lastrow = ws.Cells(ws.Rows.Count, startCell.Column).End(xlUp).Row

'Dim Lastrows As Range
'Dim countRows As Long
'countRows = ActiveSheet.UsedRange.Rows.Count
'Set myRange = ActiveSheet.UsedRange.Rows(2).Resize(countRows - 1)


On Error Resume Next
Set Rng = Range(startCell2, Columns(startCell2.Column).Find(0, lookat:=xlWhole)(0))
'Set Rng = Range(startCell, Columns(startCell.Column).Find("lalala")(0))
If Rng Is Nothing Then Set Rng = Range(startCell, Cells(Lastrow, startCell.Column))
On Error GoTo 0
Rng.Select
Rng.Name = "Projects"

here is my VBA code for you to understand better

 

[asddsaasddas]

In what way was it not working?

Try this :

Set Rng = Range(startCell2, Columns(startCell2.Column).Find(0, lookat:=xlWhole)(0))

Hi, your code is working now didnt notice i changed startCell2 to startCell. However, the 0 in my list of data is different from keying in a "0" in a cell. Your code works if i manually key in a "0 "in the cell. I do not know how the "0" came about in my column thus, I am thinking of a way to remove it. Instead of searching for a "0" and taking everything above it. Can you configure the code in a way that it counts the number of rows and subtract 1 from the total number of rows and select everything else. Thanks

 

[footoo]

I'm sorry, I don't understand.

What do you mean by "I do not know how the "0" came about in my column" ?
Also : "the 0 in my list of data is different from keying in a "0" ". What is actually in the cell?

"Can you configure the code in a way that it counts the number of rows and subtract 1 from the total number of rows and select everything else."
Count from where to where?

 

[asddsaasddas]

I'm sorry, I don't understand.

What do you mean by "I do not know how the "0" came about in my column" ?
Also : "the 0 in my list of data is different from keying in a "0" ". What is actually in the cell?

"Can you configure the code in a way that it counts the number of rows and subtract 1 from the total number of rows and select everything else."
Count from where to where?

The code will only work if I enter "0" in the cell manually. The rows of value that appear in the column is derived by another macro. The code does not take into account the "0" that was derived by another macro. However, if i typed in 0 manually in one of the cell in the column, the code will work. So, my solution to this is to count the number of rows in that column and subtract 1 row from the column. E.g. If column A has 10 rows, the 10th row would always be "0". By subtracting 1 row from column A, I will be left with 9 rows, 1-9. I want to be able to select rows 1-9 and give it a name range. Hope this is clear enough for you.

 

[footoo]

The other macro must be putting something other than 0.

Can you check what it is actually putting by checking the other macro?
Also, you can check by using functions such as LEN, CODE, TRIM [e.g. =TRIM(cell ref)=0], etc.

So, my solution to this is to count the number of rows in that column and subtract 1 row from the column. E.g. If column A has 10 rows, the 10th row would always be "0". By subtracting 1 row from column A, I will be left with 9 rows, 1-9. I want to be able to select rows 1-9 and give it a name range.
Use the code previously provided :

Set Rng = Range(startCell, Cells(Lastrow, startCell.Column)(0))

 

[asddsaasddas]

The other macro must be putting something other than 0.

Can you check what it is actually putting by checking the other macro?
Also, you can check by using functions such as LEN, CODE, TRIM [e.g. =TRIM(cell ref)=0], etc.

So, my solution to this is to count the number of rows in that column and subtract 1 row from the column. E.g. If column A has 10 rows, the 10th row would always be "0". By subtracting 1 row from column A, I will be left with 9 rows, 1-9. I want to be able to select rows 1-9 and give it a name range.
Use the code previously provided :

Set Rng = Range(startCell, Cells(Lastrow, startCell.Column)(0))

If i am not mistaken, there is a formula used to derived the values in the cell. I am not very good with excel so I do not know what the formula does. 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?

 

[footoo]

If i am not mistaken, there is a formula used to derived the values in the cell. I am not very good with excel so I do not know what the formula does. 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?

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?