Dear Dave,
So on.. did not mean any pattern.. I actualy wanted the code that would
include both sheetname and cell's address. You solved my problem..
Thanks..
Dave Peterson wrote:
> What does "and so on" mean?
>
> Is there a pattern?
>
> If not, you could just add some more checks.
>
> Dim sh As Worksheet
> Dim cmt As Comment
>
> For Each sh In ThisWorkbook.Worksheets
>
> select case ucase(sh.name)
> case is = "SHEET1", "SHEET99"
> For Each cmt In sh.Comments
> select case ucase(cmt.parent.address(0,0))
> case is = "A2", "B1"
> 'do nothing
> case else
> cmt.Delete
> end select
> Next cmt
>
> case is = "SHEET 33", "SHEET 333"
> For Each cmt In sh.Comments
> select case ucase(cmt.parent.address(0,0))
> case is = "X2", "V1"
> 'do nothing
> case else
> cmt.Delete
> end select
> Next cmt
>
> End Select
> Next sh
>
> Thulasiram wrote:
> >
> > In the previous case, the cells were fixed.. i.e. cells A2 and B1. What
> > would be the code to retain comment in A2:sheet1, V1 in sheet3, C4 in
> > sheet5 and so on..
> >
> > If I have case is = "A2", "V1", "C4" then comments in cells A2, V1 and
> > C4 in all sheet are retained.. I dont want that.. How to make the
> > condition dynamic?
> >
> > Please help!
> >
> > Thanks,
> > Thulasiram
> >
> > Thulasiram wrote:
> > > Dear Dave,
> > >
> > > Code works great! Thanks a ton!
> > >
> > >
> > > Dave Peterson wrote:
> > > > Dim sh As Worksheet
> > > > Dim cmt As Comment
> > > >
> > > > For Each sh In ThisWorkbook.Worksheets
> > > > For Each cmt In sh.Comments
> > > > select case ucase(cmt.parent.address(0,0))
> > > > case is = "A2", "B1"
> > > > 'do nothing
> > > > case else
> > > > cmt.Delete
> > > > end select
> > > > Next cmt
> > > > Next sh
> > > >
> > > > (Watch for typos!)
> > > >
> > > > Thulasiram wrote:
> > > > >
> > > > > Hello all,
> > > > >
> > > > > Code given below deletes the comments in all the cells in the
> > > > > worksheets.
> > > > >
> > > > > How to modify the code such that comments in the cell A2 and B1 of EACH
> > > > > sheet is not deleted but delete all the other comments in the
> > > > > worksheet?
> > > > >
> > > > > Dim sh As Worksheet
> > > > > Dim cmt As Comment
> > > > >
> > > > > For Each sh In ThisWorkbook.Worksheets
> > > > > For Each cmt In sh.Comments
> > > > > cmt.Delete
> > > > > Next
> > > > > Next
> > > > >
> > > > > Thanks,
> > > > > Thulasiram
> > > >
> > > > --
> > > >
> > > > Dave Peterson
>
> --
>
> Dave Peterson
|
Similar Threads
