Run-time error '1004' Method 'Rows' of Object_Global failed

I am writing VBA code for finding last_row of next sheet inside a chart sheet, in chart I a using Chart_MouseUp and Chart_MouseDown function here I am explaining Chart_MouseUp inside Chart_MouseDown event I am taking I am taking chart start point x and y co-ordinate and and on Chart_MouseUp event I am taking chart end point x and y co-ordinate and after this process I am storing start point and end end point x co-ordinate inside next sheet "AQ" column for this purpose I have to know number of cell which is blanked inside AQ column for this I have to know last_Row of AQ cell and for this purpose I am using this code

 Dim last_Row As Integer last_Row = Sheets(5).Range("B" & Rows.count).End(xlUp).Row MsgBox last_Row

Run-time error '1004' Method 'Rows' of Object_Global failed

Whenever code like

'Sheets(5).Range("AQ4") = startX
'Sheets(5).Range("AQ5") = endX

is working inside same sheet here is Chart_MouseUp event code Please if you have an Idea to handle this problem then guide me

Private Sub Chart_MouseUp(ByVal Button As Long, ByVal Shift As Long, _ ByVal x As Long, ByVal y As Long) Dim ElementID As Long, Arg1 As Long, Arg2 As Long Dim myX As Variant, myY As Double With ActiveChart ' Pass x & y, return ElementID and Args .GetChartElement x, y, ElementID, Arg1, Arg2 ' Did we click over a point or data label? If ElementID = xlSeries Or ElementID = xlDataLabel Then If Arg2 > 0 Then ' Extract x value from array of x values myX = WorksheetFunction.Index _ (.SeriesCollection(Arg1).XValues, Arg2) ' Extract y value from array of y values myY = WorksheetFunction.Index _ (.SeriesCollection(Arg1).Values, Arg2) endX = CDate(myX) endY = myY ' Display message box with point information MsgBox "X = " & startX & vbCrLf _ & "Y = " & startY & vbCrLf _ & "X = " & endX & vbCrLf _ & "Y = " & endY 'Sheets(5).Range("AQ4") = startX 'Sheets(5).Range("AQ5") = endX Dim last_Row As Integer 'Dim sh As Worksheet last_Row = Sheets(5).Range("B" & Rows.count).End(xlUp).Row MsgBox last_Row End If End If End With
End Sub

1 Answer

Explanation:

Your problem is not entirely clear, but the issue seems to be that you are receiving the 1004 error at this line in your code: last_Row = Sheets(5).Range("B" & Rows.count).End(xlUp).Row.

If that's the case, and assuming that your chart is a "Chart Sheet" (i.e. a separate Worksheet that contains only a Chart), and not a Chart that is simply embedded in a "Standard Sheet" (i.e. a Worksheet that contains rows and columns), then that error would be expected.

The reason for this is that the Rows object is not available when the Active Sheet is a "Chart Sheet", because such a sheet does not contain any rows (or columns). Clearly, that "Chart Sheet" is indeed the Active Sheet while your code is running, because your code runs at the firing of the Chart's Mouse_Up event.

Resolution:

The resolution for this issue is simple. Assuming that Worksheets(5) is a sheet that does contain rows and columns, replace the problem line of code with this one:

last_Row = Sheets(5).Range("B" & ThisWorkbook.Worksheets(5).Rows.Count).End(xlUp).Row

Or, a better alternative may be to replace the problem line with these instead:

Dim sh As Worksheet: Set sh = ThisWorkbook.Worksheets(5)
last_Row = sh.Range("B" & sh.Rows.Count).End(xlUp).Row

Note:

Your code is using four variables that have not been declared (two of which have not even been assigned a value). This is not related to the 1004 error, but it is good coding practice to declare variables before they are used.

To help avoid this issue in the future, you can include an Option Explicit statement at the top of your Module, which will force all variables in that Module to be declared. You can read more about it here.

To do this automatically for every new Module that is created, enable this option in the VBA Editor: Tools -> Options -> Editor Tab -> Require Variable Declaration