Solved: Problem with Formula to Determine the Max Row

[Cyberdude]

I used the statement:
MaxRow = Cells(Rows.Count, ?A?).Row
thinking that it would return the value 65536. However it doesn?t if I have a chart selected at the time the statement executes.

I have a macro that adds labels to a chart Series, and sometimes the statement fails and other times it works OK. The only thing I?ve found so far is that it appears that a chart is selected when the statement fails. What am I missing here??

Perhaps a better question is, under what conditions will this statement fail?

[Norie]

Charts don't have a Cells property (well I can't find one in the Object Browser) so this is probably cause an error.

Perhaps you should add a worksheet reference to explicitly tell VBA where Cells is.

[mdmackillop]

MaxRow = sheets(1).Cells.Rows.Count

[Cyberdude]

Hi, Norie! Thanx for the reply.

I added the worksheet reference like this:
Msgbox Worksheets("Main").Cells(Rows.Count, ?A?).Row
When a chart was not selected, the statement worked. When I selected a chart, the statement failed. (Sigh)

[Cyberdude]

Malcolm, you get the prize! Your solution did indeed work as advertised. Thanks for the education.

[mdmackillop]

Hi Sid,
Can you post a bit more of the code? What do you mean by "when I selected a chart"?

[Norie]

Sid

There's another problem with the code - a chart doesn't have a Rows property either.

So you should probably add a sheet reference there too.

[Bob Phillips]

MaxRow = sheets(1).Cells.Rows.Count

And what happens if Sheets(1) is a chart? You must be explicit



MaxRow = Worksheets(1).Cells.Rows.Count

[mdmackillop]

:whip

[johnske]

It will indeed error out if a chartsheet is the active sheet when the code runs. Try...

With Sheets("Main")
If ActiveSheet.Type = xlWorksheet Then
MsgBox .Cells(Rows.Count, "A").Row
Else
.Activate
MsgBox .Cells(Rows.Count, "A").Row
End If
End With

[Cyberdude]

It will indeed error out if a chartsheet is the active sheet when the code runs. Try...


VBA:

With Sheets("Main")
If ActiveSheet.Type = xlWorksheet Then
MsgBox .Cells(Rows.Count, "A").Row
Else
.Activate
MsgBox .Cells(Rows.Count, "A").Row
End If
End With

VBA tags courtesy of www.thecodenet.com (http://www.thecodenet.com/)




(http://www.thecodenet.com/)

johnske, your solution is foolproof IF I know the name of the sheet (?Main? in your example). My problem is that the code is in a column-determination UDF that doesn?t have an argument containing the name of the worksheet. As all of you have pointed out, ?ActiveSheet? isn?t foolproof.

I discovered this problem when I was running a utility macro that will display some info about a ?selected? chart. The idea is that I would manually click on a chart to select it, then I would run the ?show and tell? macro to Msgbox the info I was seeking. The processing involved determining the columns that the embedded chart covered (without explaining why). So I had manually selected the chart and the column-determination logic ran into trouble because it was executing while the chart was selected. Since the UDF was meant to be a general utility that I use in a lot of my workbooks, the utility doesn?t know the name of the worksheet it is working on because in most applications it isn?t relevant.

The column-determination UDF is not specialized for chart use. It can be used in many other cases not involving charts. I suppose I can do either of two things:
1. Add an optional argument to provide the sheet name for those applications that have a chart active when the UDF is executed.
-OR-
2. Determine which chart is active, activate it?s worksheet, call the UDF, then reactivate the chart.

When I wrote the UDF (which I?ve been using for over a year without incident) it never occurred to me that some day it might be executed when a chart is active. Phffft!

My thanks to all of you who have offered solutions.