none
Finding the last Used Cell in a Range in a PowerPoint Chart's Data RRS feed

  • Question

  • Hello,

    I'm getting an "Object Required" error with the following code at the following line:  NextRow = dta.Cells(Rows.Count, 1).End(xlUp).row + 1.  I believe the reason is that the "Cells" property needs a Range object, but I'm not sure how to handle this.  What I'm trying to do is find the next empty row. If anyone can suggest a fix or a different way of doing this, I'd much appreciate the input. Thanks.

    Sub ManipulateChart()
        Dim shp As Variant
        Dim newslID As Long
        Dim rowCntr As Integer
        Dim urc As Integer
        Dim j As Long, i As Long
        Dim rws As Variant
        Dim sld As Slide
        Dim dta As Variant
        Dim NextRow As Long
       
       
        For Each sld In ActivePresentation.Slides
            If sld.SlideID <> 1100 And sld.SlideID <> 1201 And sld.SlideID <> 1239 Then
                For Each shp In sld.Shapes
                rowCntr = 0
                NextRow = 0
                    If shp.Type = msoChart Then
                        If shp.Chart.ChartType = -4111 Or shp.Chart.ChartType = 65 Then
                            shp.Chart.ChartData.Activate
                            Set dta = shp.Chart.ChartData.Workbook.ActiveSheet
                            NextRow = dta.Cells(Rows.Count, 1).End(xlUp).row + 1
                            Set rws = dta.UsedRange.Rows
                                For j = 1 To rws.Count
                                    If dta.UsedRange.EntireRow(j).Hidden = True Then
                                    rowCntr = rowCntr + 1
                                    End If
                                Next j
                            dta.UsedRange.EntireRow(rowCntr + 1 + 1).Hidden = True 'Add a 1 for the next row after the last hidden row and 1 for the header
                            dta.ListObjects("Table1").ListRows.Add
                            dta.Cells(NextRow, 1) = "Q3 '12"
                            shp.Chart.ChartData.Workbook.Close
                        End If
                    End If
                Next shp
            End If

    Wednesday, September 5, 2012 7:52 PM

Answers

  • a quick suggestion.

    NextRow = dta.Cells(dta.Rows.Count, 1).End(xlUp).row + 1

    Rows.Count is property of both range/sheet.But it need to be referred otherwise defaults to activesheet.

    And you try using With...End With.because you have many times used dta


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    • Marked as answer by Looshsmoot Friday, September 7, 2012 4:25 PM
    Thursday, September 6, 2012 6:10 AM
    Answerer

All replies

  • a quick suggestion.

    NextRow = dta.Cells(dta.Rows.Count, 1).End(xlUp).row + 1

    Rows.Count is property of both range/sheet.But it need to be referred otherwise defaults to activesheet.

    And you try using With...End With.because you have many times used dta


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    • Marked as answer by Looshsmoot Friday, September 7, 2012 4:25 PM
    Thursday, September 6, 2012 6:10 AM
    Answerer
  • Thank you!  This was the fix!
    Friday, September 7, 2012 4:25 PM