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

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

asddsaasddas

New Member
Joined
Mar 23, 2020
Messages
37
Office Version
2016, 2013
Platform
Windows
Post the code you tried.
What sort of data is in the column?
VBA Code:
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

Well-known Member
Joined
Sep 21, 2016
Messages
2,828
Office Version
2016
Platform
Windows
In what way was it not working?

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

asddsaasddas

New Member
Joined
Mar 23, 2020
Messages
37
Office Version
2016, 2013
Platform
Windows
In what way was it not working?

Try this :
VBA Code:
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.

VBA Code:
'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

New Member
Joined
Mar 23, 2020
Messages
37
Office Version
2016, 2013
Platform
Windows
In what way was it not working?

Try this :
VBA Code:
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

Well-known Member
Joined
Sep 21, 2016
Messages
2,828
Office Version
2016
Platform
Windows
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

New Member
Joined
Mar 23, 2020
Messages
37
Office Version
2016, 2013
Platform
Windows
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

Well-known Member
Joined
Sep 21, 2016
Messages
2,828
Office Version
2016
Platform
Windows
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 :
VBA Code:
Set Rng = Range(startCell, Cells(Lastrow, startCell.Column)(0))
 

asddsaasddas

New Member
Joined
Mar 23, 2020
Messages
37
Office Version
2016, 2013
Platform
Windows
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 :
VBA Code:
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

Well-known Member
Joined
Sep 21, 2016
Messages
2,828
Office Version
2016
Platform
Windows
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?
 
Last edited:

Forum statistics

Threads
1,089,220
Messages
5,406,927
Members
403,113
Latest member
ms_excel_recal_or_die

This Week's Hot Topics

Top