Lookup last valid row

[grahambza]

Hi
I have a list of approx 130 suppliers that I want to use as my list for a drop down box
In my range for the drop down I make the range bigger in case I add suppliers

What I want to do is
- have a formula work out which is the last valid row
- use this cell ref in my sort and range formulas

Any tips?

Graham

 

[grahambza]

I used countif(A1:A200,"*") to get the answer

How do i use this result in a sort macro

Currently my macro contains
ActiveWorkbook.Names.Add Name:="Suppliers", RefersToR1C1:= _
"=Sheet3!R1C1:R200C1"

Any help is appreciated

 

[mae0429]

How to find the last row in multiple ways:
http://vbaexpress.com/forum/showthread.php?t=9774

 

[VoG]

Do you mean that you need VBA to return the last filled row in column A? If so

Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row

 

[davesweep]

Hi,

Create a named range. If your data is in column A for example, use
=OFFSET($A$1,0,0,1,COUNTA(Sheet1!A:A))
then use the range name as the definition for your validation list.

 

[grahambza]

Thanks everyone

How do I use the result of the Last Row in a formula?

I tried this:

Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row

Range("A1:LR").Select
ActiveWorkbook.Names.Add Name:="Suppliers", RefersToR1C1:= _
"=Sheet3!R1C1:LRC1"
'"=Sheet3!R1C1:R200C1"

And of course I got an error in the range selection

Any help is appreciated

 

[grahambza]

Please ignore my last post
I worked out how to use the offset suggested by VOG II - and now have another problem which i will post separately

 

[VoG]

Try this

Sub test()
Dim LR As Long
With Sheets("Sheet1")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    ThisWorkbook.Names.Add Name:="Suppliers", RefersTo:=.Range("A1:A" & LR)
End With
End Sub

 

[grahambza]

Thanks VOG II - worked like a charm