Rows.Count works on same sheet but not on another, problem?
Hello
For testing purposes, I used this code for just one sheet:
It worked perfectly
num = Range("A1").Offset(Rows.Count - 1, 0).End(xlUp).Row
Now, this should be run from another sheet, so I included:
But excel is throwing me 1004 error messages and I am lost,num = Worksheets(1).Range("A1").Offset(Worksheets(1).Rows.Count - 1, 0).End(xlUp).Row
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
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.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Hey MarvinP
The sheet is full in that column. Not one single entry missing, actually ...
It says: "Application-defined or object-defined error"
Hi,
What your exact need? and what you are trying for that? pls explain detaily.
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
Normally I would say it would just be
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?num = Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row
i.e. Worksheets(1) is the first tab(position) and not the name of the sheet or the sheet codename.
Last edited by WasWodge; 11-18-2012 at 04:50 PM.
If my solution worked (or not) please let me know. If your question is answered then please remember to mark it solved
Computers are like air conditioners. They work fine until you start opening windows. ~Author Unknown
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.
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)?
Hey WasWodge
Thanks man, your solution above worked like a charm
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
nivoe 
Register To Reply
Bookmarks