Hi, I've been trying to figure out how to use these suggestions, but none of
them work. I've put them as a cell formula, vba and functions but I always
get #value or #name or something. I know this is my error but I'm beyond
frustrated and can't figure out what I'm missing. Can someone please tell me
what I'm doing wrong, and exactly how these would get used?

lastRowNo = Range("A65536").End(xlUp).Row
Range("A2"),End(xlDown).Row if there are no blanks in the data,
otherwise
Cells(Rows.Count,"A").End(xlUp).Row

Tx, I see no reason why lastRowNo = Range("A65536").End(xlUp).Row would
ever give an error in VBA unless you've Dimmed lastRowNo as something
other than a Long. When returning a row value to a variable, the
variable should be Dimmed as a Long. In your
Range("A2").End(xlDown).Row, you've got a comma before End instead of a
period. I see no problem with the last one. These are for VBA, not
for cell formulas. There should be comparable formulas for use in
cells, but I'm not familiar with those. James

TxVics wrote:
Hi, I've been trying to figure out how to use these suggestions, but none of
them work. I've put them as a cell formula, vba and functions but I always
get #value or #name or something. I know this is my error but I'm beyond
frustrated and can't figure out what I'm missing. Can someone please tell me
what I'm doing wrong, and exactly how these would get used?

lastRowNo = Range("A65536").End(xlUp).Row
Range("A2"),End(xlDown).Row if there are no blanks in the data,
otherwise
Cells(Rows.Count,"A").End(xlUp).Row

I appreicate the input, but I'm not even that far - if I were to create this
in VBA, exactly how does it get entered? Meaning, what goes before and
after this line? If i put it in with just the exact code below:

lastRowNo = Range("A65536").End(xlUp).Row


Then it still doesn't show up as a function or a macro, it's just text.
What I really need is to see the whole code that goes into VBA. Sorry to be
so basic on this, but I'm kind of winging the scripting thing.

Thank you!




"Zone" wrote:

Tx, I see no reason why lastRowNo = Range("A65536").End(xlUp).Row would
ever give an error in VBA unless you've Dimmed lastRowNo as something
other than a Long. When returning a row value to a variable, the
variable should be Dimmed as a Long. In your
Range("A2").End(xlDown).Row, you've got a comma before End instead of a
period. I see no problem with the last one. These are for VBA, not
for cell formulas. There should be comparable formulas for use in
cells, but I'm not familiar with those. James

TxVics wrote:
Hi, I've been trying to figure out how to use these suggestions, but none of
them work. I've put them as a cell formula, vba and functions but I always
get #value or #name or something. I know this is my error but I'm beyond
frustrated and can't figure out what I'm missing. Can someone please tell me
what I'm doing wrong, and exactly how these would get used?

lastRowNo = Range("A65536").End(xlUp).Row
Range("A2"),End(xlDown).Row if there are no blanks in the data,
otherwise
Cells(Rows.Count,"A").End(xlUp).Row

Tx, If you do not have a module created yet, follow these steps:
1. With the spreadsheet on the screen, press Alt-F11
2. Select Insert from the menubar, then Module.

Copy the code below and paste it into your module (beginning with the
line Sub Test()
and ending with the line End Function).
Then Press Alt-F11 to return to the spreadsheet.

Sub Test()
MsgBox LROW(Range("a65536"))
End Sub

Function LROW(StartRg As Range)
LROW = StartRg.End(xlUp).Row
End Function

This code contains a subroutine and a user-defined function. Functions
return a value, subroutines do not.

To run the subroutine from the spreadsheet, click on Tools on the
menubar, then Macro, then Macros. A box will appear with Test
selected. Click on Run. The Test subroutine will run and "call" the
function, which will return a value to the subroutine and the message
box will display it.

You can also call the function directly from the spreadsheet. In a
blank cell, type this:
=LROW(A65536)
then press the Enter key.
The cell will display the row number of the first cell above cell 65536
that has something in it. You can replace A65536 with any other cell
address. Note that the function will look upward from the cell address
you put in (the function's "argument") until it finds a cell above it
(in the same column) that has something in it. Or, if the cell address
you use has something in it, the function will look upward until it
finds a cell without something in it. If the function doesn't find
what it's looking for, it will go all the way to the top row and return
a 1. Hope this helps you. James

TxVics wrote:
I appreicate the input, but I'm not even that far - if I were to create this
in VBA, exactly how does it get entered? Meaning, what goes before and
after this line? If i put it in with just the exact code below:

lastRowNo = Range("A65536").End(xlUp).Row


Then it still doesn't show up as a function or a macro, it's just text.
What I really need is to see the whole code that goes into VBA. Sorry to be
so basic on this, but I'm kind of winging the scripting thing.

Thank you!




"Zone" wrote:

Tx, I see no reason why lastRowNo = Range("A65536").End(xlUp).Row would
ever give an error in VBA unless you've Dimmed lastRowNo as something
other than a Long. When returning a row value to a variable, the
variable should be Dimmed as a Long. In your
Range("A2").End(xlDown).Row, you've got a comma before End instead of a
period. I see no problem with the last one. These are for VBA, not
for cell formulas. There should be comparable formulas for use in
cells, but I'm not familiar with those. James

TxVics wrote:
Hi, I've been trying to figure out how to use these suggestions, but none of
them work. I've put them as a cell formula, vba and functions but I always
get #value or #name or something. I know this is my error but I'm beyond
frustrated and can't figure out what I'm missing. Can someone please tell me
what I'm doing wrong, and exactly how these would get used?

lastRowNo = Range("A65536").End(xlUp).Row
Range("A2"),End(xlDown).Row if there are no blanks in the data,
otherwise
Cells(Rows.Count,"A").End(xlUp).Row

Thank you, thank you, thank you!!! I just couldn't make the connection
between "Function" and the entry in the actual cell. This works perfectly
now, thanks so much for taking the time to break it down for me!



"Zone" wrote:

Tx, If you do not have a module created yet, follow these steps:
1. With the spreadsheet on the screen, press Alt-F11
2. Select Insert from the menubar, then Module.

Copy the code below and paste it into your module (beginning with the
line Sub Test()
and ending with the line End Function).
Then Press Alt-F11 to return to the spreadsheet.

Sub Test()
MsgBox LROW(Range("a65536"))
End Sub

Function LROW(StartRg As Range)
LROW = StartRg.End(xlUp).Row
End Function

This code contains a subroutine and a user-defined function. Functions
return a value, subroutines do not.

To run the subroutine from the spreadsheet, click on Tools on the
menubar, then Macro, then Macros. A box will appear with Test
selected. Click on Run. The Test subroutine will run and "call" the
function, which will return a value to the subroutine and the message
box will display it.

You can also call the function directly from the spreadsheet. In a
blank cell, type this:
=LROW(A65536)
then press the Enter key.
The cell will display the row number of the first cell above cell 65536
that has something in it. You can replace A65536 with any other cell
address. Note that the function will look upward from the cell address
you put in (the function's "argument") until it finds a cell above it
(in the same column) that has something in it. Or, if the cell address
you use has something in it, the function will look upward until it
finds a cell without something in it. If the function doesn't find
what it's looking for, it will go all the way to the top row and return
a 1. Hope this helps you. James

TxVics wrote:
I appreicate the input, but I'm not even that far - if I were to create this
in VBA, exactly how does it get entered? Meaning, what goes before and
after this line? If i put it in with just the exact code below:

lastRowNo = Range("A65536").End(xlUp).Row


Then it still doesn't show up as a function or a macro, it's just text.
What I really need is to see the whole code that goes into VBA. Sorry to be
so basic on this, but I'm kind of winging the scripting thing.

Thank you!




"Zone" wrote:

Tx, I see no reason why lastRowNo = Range("A65536").End(xlUp).Row would
ever give an error in VBA unless you've Dimmed lastRowNo as something
other than a Long. When returning a row value to a variable, the
variable should be Dimmed as a Long. In your
Range("A2").End(xlDown).Row, you've got a comma before End instead of a
period. I see no problem with the last one. These are for VBA, not
for cell formulas. There should be comparable formulas for use in
cells, but I'm not familiar with those. James

TxVics wrote:
Hi, I've been trying to figure out how to use these suggestions, but none of
them work. I've put them as a cell formula, vba and functions but I always
get #value or #name or something. I know this is my error but I'm beyond
frustrated and can't figure out what I'm missing. Can someone please tell me
what I'm doing wrong, and exactly how these would get used?

lastRowNo = Range("A65536").End(xlUp).Row
Range("A2"),End(xlDown).Row if there are no blanks in the data,
otherwise
Cells(Rows.Count,"A").End(xlUp).Row

Tx, It is enjoyable to be able to help someone, and even more fun when
they reply with good news. You might want to check out John
Walkenbach's books such as Excel 2003 Power Programming with VBA. Good
luck! James

TxVics wrote:
Thank you, thank you, thank you!!! I just couldn't make the connection
between "Function" and the entry in the actual cell. This works perfectly
now, thanks so much for taking the time to break it down for me!



"Zone" wrote:

Tx, If you do not have a module created yet, follow these steps:
1. With the spreadsheet on the screen, press Alt-F11
2. Select Insert from the menubar, then Module.

Copy the code below and paste it into your module (beginning with the
line Sub Test()
and ending with the line End Function).
Then Press Alt-F11 to return to the spreadsheet.

Sub Test()
MsgBox LROW(Range("a65536"))
End Sub

Function LROW(StartRg As Range)
LROW = StartRg.End(xlUp).Row
End Function

This code contains a subroutine and a user-defined function. Functions
return a value, subroutines do not.

To run the subroutine from the spreadsheet, click on Tools on the
menubar, then Macro, then Macros. A box will appear with Test
selected. Click on Run. The Test subroutine will run and "call" the
function, which will return a value to the subroutine and the message
box will display it.

You can also call the function directly from the spreadsheet. In a
blank cell, type this:
=LROW(A65536)
then press the Enter key.
The cell will display the row number of the first cell above cell 65536
that has something in it. You can replace A65536 with any other cell
address. Note that the function will look upward from the cell address
you put in (the function's "argument") until it finds a cell above it
(in the same column) that has something in it. Or, if the cell address
you use has something in it, the function will look upward until it
finds a cell without something in it. If the function doesn't find
what it's looking for, it will go all the way to the top row and return
a 1. Hope this helps you. James

TxVics wrote:
I appreicate the input, but I'm not even that far - if I were to create this
in VBA, exactly how does it get entered? Meaning, what goes before and
after this line? If i put it in with just the exact code below:

lastRowNo = Range("A65536").End(xlUp).Row


Then it still doesn't show up as a function or a macro, it's just text.
What I really need is to see the whole code that goes into VBA. Sorry to be
so basic on this, but I'm kind of winging the scripting thing.

Thank you!




"Zone" wrote:

Tx, I see no reason why lastRowNo = Range("A65536").End(xlUp).Row would
ever give an error in VBA unless you've Dimmed lastRowNo as something
other than a Long. When returning a row value to a variable, the
variable should be Dimmed as a Long. In your
Range("A2").End(xlDown).Row, you've got a comma before End instead of a
period. I see no problem with the last one. These are for VBA, not
for cell formulas. There should be comparable formulas for use in
cells, but I'm not familiar with those. James

TxVics wrote:
Hi, I've been trying to figure out how to use these suggestions, but none of
them work. I've put them as a cell formula, vba and functions but I always
get #value or #name or something. I know this is my error but I'm beyond
frustrated and can't figure out what I'm missing. Can someone please tell me
what I'm doing wrong, and exactly how these would get used?

lastRowNo = Range("A65536").End(xlUp).Row
Range("A2"),End(xlDown).Row if there are no blanks in the data,
otherwise
Cells(Rows.Count,"A").End(xlUp).Row