Excel VBA - Copy row from table without last column

Question

I have recently converted an excel sheet entries into a table. I have a need to copy the data over to PowerPoint, as seen in code 1 below. However, I do not need the entire row, only, lets say, all columns but the last one in table 1 below.

With reference to table 1, what should be copied would be:

1  | Lorem

Originally, when it was not a table, I could just do what was now commented. But currently, it being a table, with "ActiveSheet.ListObjects("table1").ListRows(1).Range" I can only select the whole rows (I want the whole row minus the last column) . Adding ".cells" behind it only selects a specific cell and not the row.

Is there a way to copy row from table without the last column, or must I change the copy and paste method?

Table 1:

ID | SomeData | HashForUpdates 
1  | Lorem    | b1Z3p          
2  | Ipsum    | a832H

Code 1

'Code 1
Dim ptApp As Object
Dim ptPres As Object
Dim ppSlide As Object
Dim totalCols As Integer

slideNo = 1
Set ptApp = CreateObject("PowerPoint.Application")
Set ptPres = ptApp.Presentations.Add
Set ptSlide = ptPres.Slides.Add(Index:=slideNo, Layout:=4)

totalCols = ActiveSheet.ListObjects("Table 1").ListColumns.Count    'ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
totalCols = totalCols - 1

Set aShape = ptSlide.Shapes.AddTable(1, totalCols)
Set Rng = ActiveSheet.ListObjects("table1").ListRows(1).Range 'ActiveSheet.Range(Cells(1, 1), Cells(1, totalCols))
Rng.Copy
aShape.Table.Cell(1, 1).Shape.Select
ptApp.ActiveWindow.View.Paste

BigBen BigBen · Accepted Answer · 2020-02-12T14:13:12

Use Resize.

Set rng = ActiveSheet.ListObjects("table1").ListRows(1).Range.Resize(,totalCols)

Excel VBA - Copy row from table without last column

1 Answers