Range selection fail when integrating with PowerPoint

[Jurij]

Hi guys,

I am trying to do a simple thing - delete the rows that were copied into a spreadsheet after VBA has copied them into Powerpoint tables. Here is the code, it is run from PowerPoint (I left out the PowerPoint code since that one works normally):

Sub delete_rows()
Dim OWB As Excel.Workbook
Set OWB = GetObject("C:\readout\matrix.xlsx ")
Dim WS1 As Excel.Worksheet
Set WS1 = OWB.Worksheets(1)
Dim WS2 As Excel.Worksheet
Set WS2 = OWB.Worksheets(2)

WS1.AutoFilter.Range.Copy
WS2.Select
WS2.Range("A2").Select
OWB.ActiveSheet.Paste

WS2.Range("A3:A" & Range("A3").End(xlDown).Row).EntireRow.Delete
End Sub

The code that doesn´t work is "WS2.Range("A3:A" & Range("A3").End(xlDown).Row).EntireRow.Delete". It works normally when executed in an excel spreadsheet, but here I keep getting "Method "Range" of object "_Global" failed". Any suggestions on how to bypass this are welcomed.

Additionally, just out of curiosity:

"WS2.Range("A2").Select" didn´t work without "WS2.Select" befor it. Any ideas why that is the case?

Thank you,

Jurij

[Bob Phillips]

Try

Sub delete_rows() 
    Dim OWB As Excel.Workbook 
    Set OWB = GetObject("C:\readout\matrix.xlsx ") 
    Dim WS1 As Excel.Worksheet 
    Set WS1 = OWB.Worksheets(1) 
    Dim WS2 As Excel.Worksheet 
    Set WS2 = OWB.Worksheets(2)
    WS1.AutoFilter.Range.Copy   WS2.Range("A2") 
    WS2.Range("A3:A" & WS2.Range("A3").End(-4121).Row).EntireRow.Delete
End Sub

[Jurij]

Hi xld,

Thank you for the quick reply. Adding -4121 to End instead of xlDown worked pefrectly. Would you mind explaining the reasoning behind this code, as I do not understand why it didn´t work before.

Thanks,

J

[Bob Phillips]

I added two things. I added WS2. to Range("A3") and the -4121 instead of the constant. You cannot use Excel objects and constants in PowerPoint, you have to qualify the objects, and you either define your own constants or use the underlying value.