Delete alternate columns

[balumail75]

Hello,

In Powerpoint 2007 chartdata (in Excel), How to delete alternate column data (from B2 cell) without deleting the top header row?

Thanks for the help,

Regards,
Balu.

[dougbert]

Hi balumail75,

If this solution works for you, please mark as 'Solved' by using the using the 'Thread Tools' menu to the right, just above your post. A nice rating would be appreciated. :rotlaugh:

I don't know if you're still looking for this solution or not, but here it is.

First, I'm assuming the slide is selected that contains one or more Excel tables which you wish to process. As written, this macro will delete the alternate columns (leaving the column header in place) the same way on every table that exists on the selected slide.

If you wish to clear the data in columns 1, 3, 5, etc. (odd numbered columns), type '1' in the input box. However, if you desire to do this with columns 2, 4, 6, etc. (even-numbered columns), type '2' in the input box. Just type the numeral without the single quotes.



Option Explicit
Sub DeleteAlternateTblCols()
Dim lRow As Long
Dim lCol As Long
Dim oSd As Slide
Dim oSp As Shape
Dim iOddEven As Integer
Set oSd = ActiveWindow.View.Slide

On Error GoTo Release

iOddEven = InputBox("Delete the odd- or even-numbered columns in the slide table?" _
& vbNewLine & "Press 1 for odd, 2 for even") 'Column headers will not be affected

' Check all shapes on slide
For Each oSp In oSd.Shapes
With oSp
' Check if shape is a table
If oSp.Type = 19 Then
With .Table
For lRow = 2 To .Rows.Count
For lCol = iOddEven To .Columns.Count Step 2
With .Cell(lRow, lCol).Shape.TextFrame.TextRange
.Delete
End With
Next lCol
Next lRow
End With
End If
End With
Next oSp
Release:
Set oSp = Nothing
Set oSd = Nothing
End Sub


You can even run the macro twice, typing '1' the first time and '2' the 2nd time, to delete all the data in all the cells, but leave the cells and column headers in place. Due to a last minute change in code I discovered that whatever numeral you place in the input box is the column number that the alternate column deleting will begin. Bonus!!! :thumb

If you accidentally delete the wrong columns, you can press <Ctrl-Z> once to restore the alternate columns or twice, if you deleted all the data, to undo the results.

[dougbert]

I discovered this too late to re-edit my post above. There is a bonus feature in the code above, so I re-wrote the input box message to reflect this bonus feature. The code above works fine for deleting alternate columns (odd/even) as is. However, I made a last minute code change to replace a constant and received a side benefit to the same code that I realized after that posting.

So, now you can decide which is the first column number where you wish to begin alternate column deletion. Type that column number in the input box and the macro will begin alternate column deletion beginning with that column number and continuing on to the right to the end of the table! Sweet!

BTW: this macro works with either pasted Excel tables or native PP tables. It will ignore other shapes on the slide.

Here's the code with the new input box message, if you'd rather use this instead:



Option Explicit
Sub DeleteAlternateTblCols()
Dim lRow As Long
Dim lCol As Long
Dim oSd As Slide
Dim oSp As Shape
Dim iCol As Integer
Set oSd = ActiveWindow.View.Slide

On Error GoTo Release

iCol = InputBox("Enter the first column number to begin alternate column deletion in the slide table:")

' Check all shapes on slide
For Each oSp In oSd.Shapes
With oSp
' Check if shape is a table
If oSp.Type = 19 Then
With .Table
For lRow = 2 To .Rows.Count
For lCol = iCol To .Columns.Count Step 2
With .Cell(lRow, lCol).Shape.TextFrame.TextRange
.Delete
End With
Next lCol
Next lRow
End With
End If
End With
Next oSp
Release:
Set oSp = Nothing
Set oSd = Nothing
End Sub


Disclaimer: no column headers were harmed in the development of this macro. :rotlaugh:

[balumail75]

Hi Dougbert,

Thanks for the vba code. But my query is, The data is inside the chart of powerpoint 2007, which is in excel.

It's a Chart Table in Excel, when we right-click "Edit Data" in PPT 2007 charts.

Please help me in this, without converting it into range. (as I am linking the Table).

Thanks and Regards,
balumail75.

[dougbert]

Hi Balu,

I didn't forget about you. I've just spent the last few days attempting to code what I think you want, and failed. However, when I read your last response, I decided I'm still not sure what you want. You seem to use the terms 'chart' and 'table' interchangeably. So, let's get specific.

Linked Chart: to me, this is an Excel chart of some kind (bar, column, scatter, etc.) that has been copied/pasted as a link.

Linked Table: to me, this is a range of cells copied from Excel and pasted as a linked table (grid) with rows and columns.

I have come up with several ideas depending upon which of these 2 items you actually have. So which one of these two are you using?

Even better, if you have attempted some code that isn't working for you, please post the code. That would be of immense help in helping you.

I'll provide my suggestions after I know which one you are using.

[balumail75]

Hi Dougbert,

I created normal ppt 2007 charts. and when we right-click "edit data", the excel opens and in that sheet, data will be inside blueline (named "Table 1"), which is created as table in excel.

Regards,
Balu.