PDA

View Full Version : [SOLVED] Range selection fail when integrating with PowerPoint



Jurij
07-02-2014, 10:03 AM
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
07-02-2014, 11:10 AM
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
07-03-2014, 02:22 AM
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
07-04-2014, 01:16 PM
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.