I have a bit of code that tries to cleat some entries from a sheet if they
are more than 10 days old. Some rows have comments and a small image that
does not delete when you delete a row. I am trying to select it and delete
it. When the select is executed I get the error message.
For r = xlSheet.Cells(xlSheet.Rows.Count, 1).End(xlUp).Row To 2 Step -1
If DateDiff("d", xlSheet.Cells(r, 1), Now()) 10 Then
xlSheet.Range("G" & Trim(Str(r))).Select
If Not xlSheet.Range("G" & Trim(Str(r))).Comment Is Nothing
Then
xlSheet.Shapes.Range(xlSheet.Shapes.Count).Select
xlSheet.Shapes.Range(xlSheet.Shapes.Count).Delete
End If
xlSheet.Rows(r).EntireRow.Delete
End If
Next r
--
Thanks,
Uwe

Selecting the shape doesn't seem to be important in your code. What happens if
you just delete that line.

When I want to delete shapes in certain rows, I'll just check to see if it's in
that row.

And to make matters worse, comments are shapes, autofilter arrows are
shapes...it can get ugly really fast.

Ron de Bruin has some notes that you may want to review.
http://www.rondebruin.nl/controlsobjectsworksheet.htm

But this is how I would start:

Option Explicit
Sub testme()

Dim xlSheet As Worksheet
Dim myShape As Shape
Dim myRow As Long

Set xlSheet = Worksheets("Sheet1")

With xlSheet
For myRow = .Cells(.Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Date - .Cells(myRow, "A").Value 10 Then
If .Cells(myRow, "G").Comment Is Nothing Then
'do nothing
Else
'loop through the shapes
For Each myShape In .Shapes
If myShape.Type = msoComment Then
'skip it
Else
If myShape.TopLeftCell.Row = myRow Then
myShape.Delete
'if there's only one possible non-comment
'shape per row, then get out.
Exit For
End If
End If
Next myShape
End If
.Rows(myRow).Delete
End If
Next myRow
End With
End Sub

Some comments.

DateDiff using "d" is the same as a simple subtraction. But I did drop the time
from my subtraction--you used Now and I used Date.

VBA is very forgiving.

You don't need: xlSheet.Range("G" & Trim(Str(r)))
You could use: xlSheet.Range("G" & r)

Personally, I find xlsheet.cells(r,"G") just as nice

And by using With/End With, I can avoid having to type XLSheet on all those
lines.

and
xlsheet.rows(r).entirerow.delete
is equivalent to
xlsheet.rows(r).delete





Uwe wrote:

I have a bit of code that tries to cleat some entries from a sheet if they
are more than 10 days old. Some rows have comments and a small image that
does not delete when you delete a row. I am trying to select it and delete
it. When the select is executed I get the error message.
For r = xlSheet.Cells(xlSheet.Rows.Count, 1).End(xlUp).Row To 2 Step -1
If DateDiff("d", xlSheet.Cells(r, 1), Now()) 10 Then
xlSheet.Range("G" & Trim(Str(r))).Select
If Not xlSheet.Range("G" & Trim(Str(r))).Comment Is Nothing
Then
xlSheet.Shapes.Range(xlSheet.Shapes.Count).Select
xlSheet.Shapes.Range(xlSheet.Shapes.Count).Delete
End If
xlSheet.Rows(r).EntireRow.Delete
End If
Next r
--
Thanks,
Uwe

--

Dave Peterson

Actually I think I know the issue. I have a cell with a comment that has the
small red triangle in the corner. That cell also has a small image (Inserted
as a picture) the reason for the error is that it's trying to select the red
triangle, what I want is to select the image because the image does not
delete when you delete a row.
I know there are 600+ shapes, but how do I identify the image in the cell
that I'm trying to delete?
--
Thanks,
Uwe


"Dave Peterson" wrote:

Selecting the shape doesn't seem to be important in your code. What happens if
you just delete that line.

When I want to delete shapes in certain rows, I'll just check to see if it's in
that row.

And to make matters worse, comments are shapes, autofilter arrows are
shapes...it can get ugly really fast.

Ron de Bruin has some notes that you may want to review.
http://www.rondebruin.nl/controlsobjectsworksheet.htm

But this is how I would start:

Option Explicit
Sub testme()

Dim xlSheet As Worksheet
Dim myShape As Shape
Dim myRow As Long

Set xlSheet = Worksheets("Sheet1")

With xlSheet
For myRow = .Cells(.Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Date - .Cells(myRow, "A").Value 10 Then
If .Cells(myRow, "G").Comment Is Nothing Then
'do nothing
Else
'loop through the shapes
For Each myShape In .Shapes
If myShape.Type = msoComment Then
'skip it
Else
If myShape.TopLeftCell.Row = myRow Then
myShape.Delete
'if there's only one possible non-comment
'shape per row, then get out.
Exit For
End If
End If
Next myShape
End If
.Rows(myRow).Delete
End If
Next myRow
End With
End Sub

Some comments.

DateDiff using "d" is the same as a simple subtraction. But I did drop the time
from my subtraction--you used Now and I used Date.

VBA is very forgiving.

You don't need: xlSheet.Range("G" & Trim(Str(r)))
You could use: xlSheet.Range("G" & r)

Personally, I find xlsheet.cells(r,"G") just as nice

And by using With/End With, I can avoid having to type XLSheet on all those
lines.

and
xlsheet.rows(r).entirerow.delete
is equivalent to
xlsheet.rows(r).delete





Uwe wrote:

I have a bit of code that tries to cleat some entries from a sheet if they
are more than 10 days old. Some rows have comments and a small image that
does not delete when you delete a row. I am trying to select it and delete
it. When the select is executed I get the error message.
For r = xlSheet.Cells(xlSheet.Rows.Count, 1).End(xlUp).Row To 2 Step -1
If DateDiff("d", xlSheet.Cells(r, 1), Now()) 10 Then
xlSheet.Range("G" & Trim(Str(r))).Select
If Not xlSheet.Range("G" & Trim(Str(r))).Comment Is Nothing
Then
xlSheet.Shapes.Range(xlSheet.Shapes.Count).Select
xlSheet.Shapes.Range(xlSheet.Shapes.Count).Delete
End If
xlSheet.Rows(r).EntireRow.Delete
End If
Next r
--
Thanks,
Uwe

--

Dave Peterson

Thank you - My earlier reply was before I read the entire response. I'll
implement your code and I'm sure it will work.
--
Thanks,
Uwe


"Dave Peterson" wrote:

Selecting the shape doesn't seem to be important in your code. What happens if
you just delete that line.

When I want to delete shapes in certain rows, I'll just check to see if it's in
that row.

And to make matters worse, comments are shapes, autofilter arrows are
shapes...it can get ugly really fast.

Ron de Bruin has some notes that you may want to review.
http://www.rondebruin.nl/controlsobjectsworksheet.htm

But this is how I would start:

Option Explicit
Sub testme()

Dim xlSheet As Worksheet
Dim myShape As Shape
Dim myRow As Long

Set xlSheet = Worksheets("Sheet1")

With xlSheet
For myRow = .Cells(.Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Date - .Cells(myRow, "A").Value 10 Then
If .Cells(myRow, "G").Comment Is Nothing Then
'do nothing
Else
'loop through the shapes
For Each myShape In .Shapes
If myShape.Type = msoComment Then
'skip it
Else
If myShape.TopLeftCell.Row = myRow Then
myShape.Delete
'if there's only one possible non-comment
'shape per row, then get out.
Exit For
End If
End If
Next myShape
End If
.Rows(myRow).Delete
End If
Next myRow
End With
End Sub

Some comments.

DateDiff using "d" is the same as a simple subtraction. But I did drop the time
from my subtraction--you used Now and I used Date.

VBA is very forgiving.

You don't need: xlSheet.Range("G" & Trim(Str(r)))
You could use: xlSheet.Range("G" & r)

Personally, I find xlsheet.cells(r,"G") just as nice

And by using With/End With, I can avoid having to type XLSheet on all those
lines.

and
xlsheet.rows(r).entirerow.delete
is equivalent to
xlsheet.rows(r).delete





Uwe wrote:

I have a bit of code that tries to cleat some entries from a sheet if they
are more than 10 days old. Some rows have comments and a small image that
does not delete when you delete a row. I am trying to select it and delete
it. When the select is executed I get the error message.
For r = xlSheet.Cells(xlSheet.Rows.Count, 1).End(xlUp).Row To 2 Step -1
If DateDiff("d", xlSheet.Cells(r, 1), Now()) 10 Then
xlSheet.Range("G" & Trim(Str(r))).Select
If Not xlSheet.Range("G" & Trim(Str(r))).Comment Is Nothing
Then
xlSheet.Shapes.Range(xlSheet.Shapes.Count).Select
xlSheet.Shapes.Range(xlSheet.Shapes.Count).Delete
End If
xlSheet.Rows(r).EntireRow.Delete
End If
Next r
--
Thanks,
Uwe

--

Dave Peterson