I am using this code that I found from the solution of 'Find the last row in a sheet': Error in finding last used cell in Excel with VBA. How do I tweak it to answer my question? Sorry, can't fix the error to post a code here. Thank you.
Asked
Active
Viewed 473 times
0
-
So do you mean you have text as well as numbers in the column, and you want to find the last number? – BigBen Nov 07 '19 at 14:43
-
You could use an array formula, for example `=MIN(IF(ISNUMBER($L$1:$L$13),ROW(L1:L13)))` – Nathan_Sav Nov 07 '19 at 15:03
-
I want to loop up to the last row whose column 1 contains just a number, not in other text. There's a long list of numbers in the column and then non-numeric text appears later. So I want to count up to the last row with a number, if that makes sense. – cd3091 Nov 07 '19 at 15:05
-
Do the loop using `IsNumeric()` on the cells contents – Nathan_Sav Nov 07 '19 at 15:06
-
I am trying to do: With Sheets("Sheet1") lastrow = .Range("A" & .Rows.Count).End(xlUp).Row lastrow=IsNumeric(Cells("A", lastrow).Value) <- error here End With I defined lastrow as Range. Is this all right? – cd3091 Nov 07 '19 at 15:47
1 Answers
2
Imagine the following data in column A:A:
Now to loop your column you can use:
Sub Test()
With Sheet.Range("A:A") 'Change to your own sheets CodeName
For Each cl In .SpecialCells(xlCellTypeConstants, 1)
Debug.Print cl.Address
Next cl
End With
End Sub
Because you say to have numeric values, we can iterate column A:A with SpecialCells. Use xlCellTypeConstants to include only cells with constants in them and add the XlSpecialCellsValue enumeration (xlNumbers or just 1 for numeric constants) which you can then loop.
It will only iterate over the cells holding numeric values. Therfor you don't need to try and find a last row, neither do you need to loop all cells and check if the value is numeric. This method will also allow for other textual data type values in between the range you working from.
If you must get the last row holding a numeric value we can alter the code a bit to look at the last Area in the Areas collection (since we could be having a dis-contiguous Range object). Now to get the last row could look like:
Sub Test()
Dim rng As Range
Dim lr As Long
With Sheet1.Range("A:A") 'Change to your own sheets CodeName
Set rng = .SpecialCells(xlCellTypeConstants, 1)
lr = rng.Areas(rng.Areas.Count).Cells(rng.Areas(rng.Areas.Count).Count, 1).Row
End With
End Sub
For readability, you might find the following easier:
Sub Test()
Dim rng As Range
Dim lr As Long
With Sheet1.Range("A:A") 'Change to your own sheets CodeName
Set rng = .SpecialCells(xlCellTypeConstants, 1)
Set rng = rng.Areas(rng.Areas.Count)
lr = rng.Cells(rng.Cells.Count).Row
End With
End Sub
This will eliminate the need to use an array formula/function to retrieve the last row holding a numeric value.
JvdV
- 70,606
- 8
- 39
- 70
-
Thank you. Actually, I get an error 'Method 'Range' of object'_Worksheet' failed for the With line. Say X1 is the column name, how do you change? – cd3091 Nov 07 '19 at 17:14
-
You'll need to write your codename down. That's different to a sheets name. You'll find it with a simple Google search. It's simply a safer way of refering to a certain sheet then it's name – JvdV Nov 07 '19 at 17:20
-
Got it. There's also an error 'No cells were found' on .SpecialCells line. Sorry for bothering you. – cd3091 Nov 07 '19 at 17:56
-
But setting Range("A:A") means the whole column A, and there are hundreds of cells with a numeric value. – cd3091 Nov 07 '19 at 18:01
-
The error says differently. Are you sure you refer to the right sheet? @cd3091 – JvdV Nov 07 '19 at 18:03
-
Yeah, I followed Objects number instead of sheet number. Sorry. I will upvote your post but it will not reflect the score since I have lower than 15 points. Thank you for your time, really appreciate it. – cd3091 Nov 07 '19 at 18:10
-
@cd3091, so it's resolved now? Consider to return the favor by actually clicking the checkmark (upvote/downvote is different). Appreciate it. – JvdV Nov 07 '19 at 18:11