Find ACTUAL last used cell in a Col of a dynamic range

[julhs]

To avoid reprimand for cross posting this is the original post that I’m diverging from:
Copy MessageBox returned value to a cell
For benefit of future hits on this, key elements are highlighted red.
Sorry for over kill of data/code & dialog, been stung in past with a lack of both. But also spent too long testing code found online that as a complete numpty/novic couldn't decipher due to a lack of detail/knowlege/understanding . Hope this will be of assistance to at least a few people. "Like" it if you find it helpfull, I'd be chuffed.
Can someone suggest a way to overcome/eliminate potential INACCURATE results?
Essentially this is a matter of having found a specific lastrow "T:T “Cash Paid” to then use “End(xlUp).Row” on THAT row to find the ACTUAL LASTED USED cell in Col S.
Have come up with (what I believe to be a convoluted solution) to return the range address that I want to use in my main code.
It is still a bit flawed because I'm using " End(xlDown).Row", it leaves it open to INACCURATE results WHEN/IF there is a blank cell somewhere in Col S BEFORE “lastrow Range("T:T")” “Cash Paid”
So by using lastrow "T:T “Cash Paid" and " End(xlUP).Row" it will would overcome INACCURATE results
The fundamental starting point is the code below; it establishes the first & last rows of the range in question.

firstrow = .Range("s:s").Find(what:="CF rules", LookIn:=xlValues, LookAt:=xlWhole).Row        '=S7
lastrow = .Range("T:T").Find(what:="Cash Paid", LookIn:=xlValues, LookAt:=xlWhole).Row  '= T342

My complete code in use is:-

Sub MeUsedRangetest()
Dim sht As Worksheet
Dim lastrow As Long, firstrow As Long
Dim LstUsedRow As Variant
Dim FstUsedCell As Variant

With ActiveSheet
firstrow = .Range("s:s").Find(what:="CF rules", LookIn:=xlValues, LookAt:=xlWhole).Row '=$A$7
lastrow = .Range("T:T").Find(what:="Cash Paid", LookIn:=xlValues, LookAt:=xlWhole).Row '=$S$342
'---------------------------------
' Ignore Next section, as is just about returning test results to the ActiveSheet (relevant in testing process, simply left in to show the process I used to get to final result )
.Range("ar340") = .Range("S" & firstrow + 3 & ":T" & lastrow - 1).CurrentRegion.Address ‘=$A$6:$S$337
.Range("as340") = .Range("S" & firstrow + 3 & ":s" & lastrow - 1).CurrentRegion.Address ‘=$A$6:$S$337
.Range("at340") = .Range("S" & firstrow + 3 & ":T" & lastrow - 1).Address ‘=$A$6:$S$337
.Range("au340") = .Range("s" & firstrow + 3 & ":s" & lastrow - 1).Address ‘=$S$10:$S$341
.Range("av340") = Range("s" & LstUsedRow).Address                        '= "$S$337"
.Range("ay340") = .Range("S" & firstrow + 3).Address                     ‘=$S$10
.Range("az340") = .Range("S" & lastrow).Address                          ‘=$S$342
.Range("ba340") = .Range("S" & lastrow - 1).Address                      ‘=$S$341
.Range("bb340") = FstUsedCell                                            '= "$S$10:$S$337"
'--------------------------------------
'This next section returns the "ACTUAL USED RANGE" of Col S, between "firstrow" and the dynamic "lastrow"
LstUsedRow = .Range("s10").End(xlDown).Row                           'This works, needs "Dim LstUsedRow As Variant"
.Range("av340") = Range("s" & LstUsedRow).Address                    ' = "$S$337"
FstUsedCell = Range("S" & firstrow + 3 & ":s" & LstUsedRow).Address 'This works, needs "Dim FstUsedCell As Variant"
.Range("BB340") = FstUsedCell                                         '= "$S$10:$S$337"
End With
'---------------------------------------
'For testing purposes only
Debug.Print firstrow '= 7
Debug.Print lastrow '= 342
Debug.Print Range("s" & firstrow + 3 & ":s" & lastrow - 1).Address '= $S$10:$S$341
Debug.Print LstUsedRow '=337
Debug.Print FstUusedCell  '= $S$10:$S$337 (is the firstrow + 3 (7+3) to ACTUAL LstUsedRow row of Col S

End Sub

 

[Alex Blakenburg]

Your link was helpful since you have the layout of your data on the other thread.
Different people are going to attack this differently.

Does "Cash Paid" only appear once in column T ?
ie Please confirm that it is not just the first of muliple repeating sections.
Is there "always" a blank row before Cash Paid ?
(or should we test for that)

Why are we looking for CF Rules, why not the heading of column S Payment Details List ?
Is there always going to be a blank row after the heading ?
Is it always going to be blank between the heading and the first data row ?

 

[julhs]

Thanks for reply Alex.
Xl2bb in the linked post, was tucked away in post #12
Going to base my answers on the Xl2bb posted here

The range between row 9 and row 23 is a dynamic range (could eventually be 500 rows). “Cash Paid” row is constantly bumped down as rows are added.
Have to use “Cash Paid” (currently row 24) as the row to reference as the LASTROW because there is a big section of data starting 2 rows below “Cash Paid”.
“Cash Paid” will never appear anywhere ABOVE where it is now, but does appear in the “Irrelevant data in this area” section below it.
Always blank rows between last data in Col S and “Cash Paid” row.
No particular reason for using the text “CF rules” in S7 could just as easily use “Bank & Cash” in header row.
The text “CF rules” S7 and the “n” in S8 & 9 are only meant to be temporary, but was only way I could get the xl Count Down to function.

So short answer is; normally 3 blank rows below header row, data ALWAYS starts in row 10, always blank rows between last data in S20 and “Cash Paid”. “Cash Paid” NEVER appears above row 24, but it DOES appear several times in “Irrelevant data in this area” section.

Ultimately I’m after the range address of the ACTUAL used range (S10:S20)

Accounts Code TestingBook1.xlsm
	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
5
6		Date	Inv #	Payment Method	Payment Details List	Bank & Cash		Drawings	Purchases of Stock / Materials	Tool,Weather/Safety equip	Repairs & Renewals	Motor Expenses	Hire Charges	Liability Insurance	N.I cont / TGWU	Gen Insur Office Postage/ Stationary	Misc	Acquisition of Assets	Let Property	Bank Charges	Utilities / House	Income Tax
7	s	Leave these 2 row empty				CF rules
8	s					n
9	s					n
10	s	Apr-07		St Order	Rent	694.50															694.50
11	s	Apr-07		D. Debit	Amazon Prime	5.99		5.99
12	s	Apr-07	gs005	My Sol	Heating Oil	540.22															540.22
13	s	Apr-08		D. Debit	EE Mobile(1)	28.21											28.21
14	s	Apr-08		D. Debit	AA membership	39.61											39.61
15	s	Apr-11	gs010	My Sol	Fuel Other	127.00						127.00
16	s	Apr-12	gs015	My Sol	Tesco	29.44		29.44
17	s	Apr-12	gs020	My Sol	Tesco	113.55		113.55
18	s	Apr-13		My Sol	Amazon Prime	7.99		7.99
19	s	Apr-19		D. Debit	EDF energy	16.25															16.25
20	s	Apr-19	gs025	My Sol	Tesco	15.00		15.00
21	s
22	s
23	s
24	s					1617.76	Cash Paid	171.97				127.00					67.82				1250.97
25	s
26	s				Irrelevant	data	in this	area
27	s				vv	hh
28	s				vc	kk
29	s				cv	mm
30					CurrentRegion.Address	$A$6:$AJ$32
31
32					.Range("s" & firstrow + 2 & ":s" & lastrow - 1).Address	$S$10:$S$23
33
April 22 - 23 (3)

Cell Formulas
Range	Formula
S24,U24:AI24	S24	=SUM(S$7:OFFSET(S24,-1,0))
AJ24	AJ24	=SUM(AJ$7:OFFSET(AI24,-1,0))
T25	T25	=SUMIF(Q$7:$Q23,$T24,$S$7:$S23)

 

[Alex Blakenburg]

I am sure others would do this in far less code but this should work for you.
Ideally the 2 exit subs would have a message with it but I didn't want to make it even longer.

Sub GetDataRows()
    Dim sht As Worksheet
    Dim lastcell As Range, firstcell As Range
    Dim lastrow As Long, firstrow As Long
    Dim findString As String
    Dim LstUsedRow As Variant
    Dim FstUsedCell As Variant
   
    Set sht = ActiveSheet
   
    With sht
        ' Get start of Data Range
        findString = "Bank & Cash"
        Set firstcell = .Range("S:S").Find(what:=findString, LookIn:=xlValues, LookAt:=xlWhole)
        If firstcell Is Nothing Then Exit Sub
       
        firstrow = firstcell.Row
       
        ' Get end of Data Range
        findString = "Cash Paid"
        Set lastcell = .Range("T:T").Find(what:=findString, LookIn:=xlValues, LookAt:=xlWhole)
        If lastcell Is Nothing Then Exit Sub
       
        Set lastcell = lastcell.Offset(, -1)        ' Move across to amount column
        If lastcell.Offset(-1) <> "" Then
            Set lastcell = lastcell.Offset(-1)
        Else
            Set lastcell = lastcell.End(xlUp)
        End If
       
        lastrow = lastcell.Row

        Debug.Print "First Row: " & firstrow
        Debug.Print "Last Row: " & lastrow
    End With

End Sub

It assumes you have removed the text you had in there just for the purpose of making your previous macro works and the 2 columns looks like this:

20230123 VBA Section Data Range julhs.xlsm
	S	T
5
6	Bank & Cash
7
8
9
10	694.5
11	5.99
12	540.22
13	28.21
14	39.61
15	127
16	29.44
17	113.55
18	7.99
19	16.25
20	15
21
22
23
24	1617.76	Cash Paid
25		0
26	data	in this
Data

Cell Formulas
Range	Formula
S24	S24	=SUM(S$7:OFFSET(S24,-1,0))
T25	T25	=SUMIF(Q$7:$Q23,$T24,$S$7:$S23)

 

[Alex Blakenburg]

Oops, the firstrow line should be:

        firstrow = firstcell.End(xlDown).Row

 

[julhs]

Well your post #5 saved an overly lengthy reply I was in process of writing.
Just leaves me to return range address for S10:S20.

You’ve been a brilliant help, thank you
julhs

 

[julhs]

Ran out of message edit time.
Line I needed is;

Range("s34") = Range("s" & firstrow & ":s" & lastrow).Address

For future readers;
Marking post #4 as solution but need the edit in post #5 to complete things

 

[Alex Blakenburg]

Thanks for the feedback and glad I could help.

In relation to your range:
It would be safer to do either of the following:-

'Option 1: prefix it with the sheet reference
sht.Range("s34") = sht.Range("s" & firstrow & ":s" & lastrow).Address

'Option 2: if it continues from the other code include it in the with / end with
With sht
     .Range("s34") = .Range("s" & firstrow & ":s" & lastrow).Address
End With