how to get the number of the last row?

[Lottoguy]

I have sheets with many numbers in many rows and columns. I m making a statistic about the time, so the number on a row below the other one means that numbers showed up later. There are numbers that are in the sheet more than once, in a different row and column, but what I need is how to get the number of the last row that number is on? I m not sure if excel has a formula for it or if I have to write a macro to do it. If I have to write a macro, tell me how to do it if you can help.

 

[JoeMo]

Here's a lightly tested macro you can try:

Sub GetLastRowNum()
Dim R As Range, x
Set R = ActiveSheet.UsedRange
On Error Resume Next
Set x = R.SpecialCells(xlCellTypeConstants, xlNumbers).Find("*", , , xlWhole, , xlPrevious)
On Error GoTo 0
If Not x Is Nothing Then
    MsgBox "LastNumber is in row " & x.Row & " and column " & x.Column & " of the used range in the active sheet"
Else
    MsgBox "Can't find last row with a number on the activesheet"
End If
    
End Sub

 

[Habtest]

Hi Lottoguy, you may also try a simple formula below, note it requires the numbers in selection are consecutive without blank or text, otherwise needs tweaks

Book1
	A	B	C
1	Number
2	9		Last number
3	6		7
4	1
5	7
6	6
7	7
8	7
9	3
10	4
11	7
Sheet2

Cell Formulas
Range	Formula
C3	C3	=INDEX(A2:A20,COUNT(A2:A20),1)

 

[Dossfm0q]

Option If there are Blank Cells

Book1
	A	B	C
1	Number
2	9		Last number
3	6		20
4	1
5	7
6		<< Blank Cell
7	7
8		<< Blank Cell
9	3
10	4
11	7
12		<< Blank Cell
13	20
14
15
Sheet2

Cell Formulas
Range	Formula
C3	C3	=LOOKUP(2,1/(NOT(ISBLANK(A:A))),A:A)

 

[Lottoguy]

Here's a lightly tested macro you can try:

Sub GetLastRowNum()
Dim R As Range, x
Set R = ActiveSheet.UsedRange
On Error Resume Next
Set x = R.SpecialCells(xlCellTypeConstants, xlNumbers).Find("*", , , xlWhole, , xlPrevious)
On Error GoTo 0
If Not x Is Nothing Then
    MsgBox "LastNumber is in row " & x.Row & " and column " & x.Column & " of the used range in the active sheet"
Else
    MsgBox "Can't find last row with a number on the activesheet"
End If
   
End Sub

This macro is showing the number of the last row and column that my last number is in my range. But its not what I need to do. What I need is to get the number of the last row that all numbers are in my range. I have numbers from 01 to 5451, in the range of A1:R594. Many numbers are in the range more than once, some are 2,3 and 4 times. I just need the number of the last row that they are. Like if a number shows up 4 times in my range, I only need to know the last row that it is.

 

[Fluff]

Is this what you mean

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	5296	2829	5295	3268	3964	5184	1103	4408	448	2062	4637	255	1487	4377	4496	2555	183	4736
2	3914	1550	4674	1231	2721	3655	3762	4159	3101	1569	1029	4397	4926	1180	2203	189	2705	3614
3	2529	3985	3192	2389	471	2919	579	221	5278	5376	3182	2750	3173	1107	1948	3856	3281	1322		1499	19
4	5200	1416	3338	3048	2249	1802	2833	1495	3156	1148	610	3323	3499	1658	4138	5143	821	4456
5	1621	3798	3084	3223	4102	3292	3791	2700	1692	930	1416	3293	3469	4430	5393	1520	2803	2913
6	4919	1417	2822	1481	986	4033	1182	127	161	3016	5155	2447	2453	4818	4331	990	17	330
7	4183	1178	1507	158	535	4857	4244	3957	5085	2210	1831	2025	1715	819	3086	4180	4622	2653
8	4105	2015	1104	2092	5286	3709	544	4205	1443	4275	1919	1670	4777	1422	4435	3327	3276	4153
9	2101	2514	2689	2622	5176	2793	3827	614	4883	2194	5371	2634	3636	4074	2598	1276	2184	379
10	1499	3408	157	266	161	5032	570	1944	516	1840	4861	1113	4581	589	2839	3503	613	2401
11	401	2155	2163	2615	5029	3396	4152	791	4859	3802	955	3686	3517	4328	1747	3428	4098	1054
12	3279	1250	2879	2836	2712	4973	4398	135	2575	1593	2409	5207	2260	4468	2246	3796	1499	4684
13	4603	4712	4025	2694	4382	884	1171	392	2928	227	1837	4152	992	2179	3782	4147	4354	1322
14	1433	323	1848	3635	1174	703	3131	808	846	134	53	2071	3460	4510	185	5357	5384	4358
15	2410	2383	2827	3682	2370	1636	3348	1125	756	3619	2920	2653	161	4511	2273	4559	3018	3441
16	2159	3597	3963	3502	212	1301	3702	1193	2150	1429	4142	253	1457	3271	4378	4888	1046	1897
17	1355	5394	4897	3347	467	4750	2637	4941	4240	52	2096	5315	2513	3922	216	5266	265	4950
18	4072	3474	3397	2200	2180	3945	266	3736	2002	1888	3558	1588	1658	3057	3843	1362	86	3107
19	439	4656	1766	1465	4317	1596	1499	4164	3953	1141	4929	2140	2891	1657	1290	1161	5288	2556
20	1802	5028	4243	151	3792	4062	968	585	2371	1913	1487	1011	5218	4039	420	507	2968	2004
21
Main

Cell Formulas
Range	Formula
U3	U3	=AGGREGATE(14,6,(ROW(A1:A20)-ROW(A1)+1)/(A1:R20=T3),1)

 

[Lottoguy]

Yes, I need the last number row that a certain number is. Do I have to write a macro?

 

[Fluff]

No, you can just use the formula.

 

[Lottoguy]

No, you can just use the formula.

what is this formula? you showed me the sheet but not the formula

 

[MARK858]

No, he showed you the formula as well at the bottom or you could hover over or click U3 to see it.