Finding last Value in A Column of a Multi-Column Dynamic Range of Cells

[Ark68]

I have a set variable name 'rng_pdaservices' to a dynamic defined range of cells. In this particular case, referring to A22:Q32.
I am looking for an efficient VBA solution to find the last occupied cell in column A within this range , ie the last cell in A22:A32 that holds a value.

Thoughts?

 

[footoo]

Dim f As Range
Set f = Intersect(Range("rng_pdaservices"), [A:A]).Find(What:="*", After:=Range("rng_pdaservices")(1), SearchDirection:=xlPrevious)

 

[Peter_SSs]

Is this what you mean?
(I have assumed at least one cell in column A of that range will hold a value)

MsgBox rng_pdaservices.Columns(1).Find(What:="*", SearchDirection:=xlPrevious).Address(0, 0)

 

[Ark68]

Thank you both for your replies. Peter pointed out something I hadn't thought of, and that was the fact that that range may not initially have any value in it. The purpose of the routine that Iseek this help for is building a list of data in the range, always building on the last cell of that range. But it may very well be that there is no value in that range yet and that this info will be needed to place the first.

How does this change your suggestions?

 

[Peter_SSs]

How does this change your suggestions?

One way would be

Dim msg As String

msg = "No data found"
On Error Resume Next
msg = rng_pdaservices.Columns(1).Find(What:="*", SearchDirection:=xlPrevious).Address(0, 0)
On Error GoTo 0
MsgBox msg

 

[Ark68]

Dim f As Range
Set f = Intersect(Range("rng_pdaservices"), [A:A]).Find(What:="*", After:=Range("rng_pdaservices")(1), SearchDirection:=xlPrevious)

Thank you Footoo, your help is appreciated!!

 

[Ark68]

Thank you Peter, this works wonderfully.

So that I don't get an unpleasant surprise, will this work ok if there is data and empty cells in that range? For instance, consider rng_pdaservices data in cells A13:A32. Will the spaces between the data cause problems, or will the answer of 26 be returned?

task allocation.xlsm
	A
13	data
14	data
15	data
16
17
18	data
19	data
20	data
21	data
22	data
23	data
24	data
25
26	data
27
28
29
30
31
32
Sheet13

 

[Peter_SSs]

Will the spaces between the data cause problems, or will the answer of 26 be returned?

Try it.

 

[Ark68]

Thanks again Peter! It might take a while before I encounter this scenario unless I can find a way to recreate it intentionally. This scenario would be very rare but not impossible.

 

[Peter_SSs]

unless I can find a way to recreate it intentionally.

That is pretty easy to do.
Run this code with the Active sheet empty (or at least nothing in A22:A32)

Sub Find_Last()
  Dim rng_pdaservices As Range
  Dim msg As String
  
  Set rng_pdaservices = Range("A22:Q32")
  msg = "No data found"
  On Error Resume Next
  msg = rng_pdaservices.Columns(1).Find(What:="*", SearchDirection:=xlPrevious).Address(0, 0)
  On Error GoTo 0
  MsgBox msg
End Sub