Post the code you tried.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.
What sort of data is in the column?
Post the code you tried.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?
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"
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.In what way was it not working?
Try this :
VBA Code:Set Rng = Range(startCell2, Columns(startCell2.Column).Find(0, lookat:=xlWhole)(0))
'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"
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. ThanksIn what way was it not working?
Try this :
VBA Code:Set Rng = Range(startCell2, Columns(startCell2.Column).Find(0, lookat:=xlWhole)(0))
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.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?
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?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))
The other macro must be getting the cell value from somewhere. Check that first.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?