Works using XL interface but not in VBA (err: 1004)
 

Hi everyone. I have an interesting problem.
I created a spreadsheet with groupings on certain rows.
I now want to delete the rows that belong to the grouping through code. To
identify the rows I want to delete I put a dummy comment on one of the cells
in the row and then run the following code to remove the rows:

For each wks in Application.ActiveWorkbook.Worksheets
wks.Activate
ActiveSheet.Cells.SpecialCells(xlCellTypeComments) .EntireRow.Delete
Next

Now it works for some sheets but not others.
I get the error message:
"Cannot use that command on overlapping selections"

Strangely, it works if I perform the steps through the XL interface.
(Edit+Go To+Special...+Comments and then choose Edit+Delete+Entire Row)

Any ideas would be very appreciated.

Thank you.

Works using XL interface but not in VBA (err: 1004)
 

This happens when you have more than one comment on a row. This works
around it:

Sub a()
Dim Area As Range
Dim Rg As Range
For Each Area In Cells.SpecialCells(xlCellTypeComments)
If Rg Is Nothing Then
Set Rg = Area.EntireRow
Else
Set Rg = Union(Rg, Area.EntireRow)
End If
Next
If Not Rg Is Nothing Then Rg.Delete
End Sub


--
Jim
"Jim Bennett" wrote in message
...
| Hi everyone. I have an interesting problem.
| I created a spreadsheet with groupings on certain rows.
| I now want to delete the rows that belong to the grouping through code. To
| identify the rows I want to delete I put a dummy comment on one of the
cells
| in the row and then run the following code to remove the rows:
|
| For each wks in Application.ActiveWorkbook.Worksheets
| wks.Activate
| ActiveSheet.Cells.SpecialCells(xlCellTypeComments) .EntireRow.Delete
| Next
|
| Now it works for some sheets but not others.
| I get the error message:
| "Cannot use that command on overlapping selections"
|
| Strangely, it works if I perform the steps through the XL interface.
| (Edit+Go To+Special...+Comments and then choose Edit+Delete+Entire Row)
|
| Any ideas would be very appreciated.
|
| Thank you.

Works using XL interface but not in VBA (err: 1004)
 

Hey thanks very much Jim.
I did have multiple comments on the same row and removing them solved the
problem but I will take your code as a better solution.

"Jim Rech" wrote:

This happens when you have more than one comment on a row. This works
around it:

Sub a()
Dim Area As Range
Dim Rg As Range
For Each Area In Cells.SpecialCells(xlCellTypeComments)
If Rg Is Nothing Then
Set Rg = Area.EntireRow
Else
Set Rg = Union(Rg, Area.EntireRow)
End If
Next
If Not Rg Is Nothing Then Rg.Delete
End Sub


--
Jim
"Jim Bennett" wrote in message
...
| Hi everyone. I have an interesting problem.
| I created a spreadsheet with groupings on certain rows.
| I now want to delete the rows that belong to the grouping through code. To
| identify the rows I want to delete I put a dummy comment on one of the
cells
| in the row and then run the following code to remove the rows:
|
| For each wks in Application.ActiveWorkbook.Worksheets
| wks.Activate
| ActiveSheet.Cells.SpecialCells(xlCellTypeComments) .EntireRow.Delete
| Next
|
| Now it works for some sheets but not others.
| I get the error message:
| "Cannot use that command on overlapping selections"
|
| Strangely, it works if I perform the steps through the XL interface.
| (Edit+Go To+Special...+Comments and then choose Edit+Delete+Entire Row)
|
| Any ideas would be very appreciated.
|
| Thank you.