Rows.Count works on same sheet but not on another, problem?

[nivoe]

Hello

For testing purposes, I used this code for just one sheet:

Please Login or Register  to view this content.

It worked perfectly
Now, this should be run from another sheet, so I included:

Please Login or Register  to view this content.

But excel is throwing me 1004 error messages and I am lost,
because I tried plenty of combinations and not a single one worked.

I think it's a minor problem, but I am too new to VBA to see it.
Thanks in advance

[MarvinP]

Hi,

It looks like you have found a sheet that has no data in that column. Then your code goes to row 1 and you want to return row Zero in "num". There is no "Row Zero" so you get an error.

[nivoe]

Hey MarvinP

The sheet is full in that column. Not one single entry missing, actually ...

It says: "Application-defined or object-defined error"

[vincentmca]

Hi,
What your exact need? and what you are trying for that? pls explain detaily.

[nivoe]

What am I trying to achieve?

- In column A, I am looking for the row number of the last cell with an entry. So, if

A20 = "String" and
A21 = ""

it would return int = 20!

That's it.
As I said, first code works perfectly on the same sheet.

But that was for testing purposes only.
I need it to be running on another sheet (2nd code), but that does not work.

Thank you guys

[WasWodge]

Normally I would say it would just be

Please Login or Register  to view this content.

but seeing as your longer code does return the last cell in the column but you get the error are you sure you are referencing the right sheet?
i.e. Worksheets(1) is the first tab(position) and not the name of the sheet or the sheet codename.

[nivoe]

Yes, I tried it also with the string reference, just in case. Same problem.

But your solution sounds promising. I will try it as soon as I get the chance again.

[WasWodge]

2 things.

1) What is the name of the sheet which you are referencing as Worksheets(1)?
2) When in the VB editor, in the properties box find your Workbook name, underneath it (in the section Microsoft Excel Objects) you will see all your sheetnames (they are the ones in brackets). What is the name that is next to your sheetname (the name that is not in brackets)?

[nivoe]

Hey WasWodge

Thanks man, your solution above worked like a charm