Excel VBA - Remove Shapes and Cell Contents

[rick6823]

Hello;

I have an Excel spreadsheet which I use to import images and creat
sort of a product catalog. It's working great.

However, I'm unable to come up with code that works fo
clearing/resetting the page. I've tried 3 or 4 different ways withou
any success.

What I need to do is clear all cell contents and shapes (images) withi
every column but "A". Anybody have a code sample that would do this??

Thanks in advance for your help. I appreciate it!

Ric

 

[Bill Renaud]

Rick: Try the following routine. It assumes that row 1 of the worksheet is
the header row of a list, so it does not clear anything in row 1, but you
should be able to modify it however you want.

Public Sub ClearData()
Dim rngDataArea As Range
Dim shprng As ShapeRange
Dim shpPicture As Shape

'Set an object reference to the data area to be cleared.
'Assume row 1 is the header of a list, so extend the range
'from cell $B$2 to the lower right corner cell on the worksheet.
With ActiveSheet.UsedRange
Set rngDataArea = .Offset(1, 1) _
.Resize(.Rows.Count - 1, .Columns.Count - 1)
End With

'Clear the data area of values, formulas, comments, and notes.
'Add or remove any methods you want or don't want.
With rngDataArea
.ClearContents
.ClearComments
.ClearNotes
End With

'Select and delete all objects (pictures, drawing objects, etc.)
'where the top left corner cell is in the data area.
'See the Shapes Collection Object in Microsoft Excel Visual Basic Help.
For Each shpPicture In ActiveSheet.Shapes
If Not (Intersect(shpPicture.TopLeftCell, rngDataArea) Is Nothing) _
Then
'Shape is in the data area to be cleared.
shpPicture.Delete
End If
Next shpPicture
End Sub