excel question, using comments in a filtered list 1

[harleynana]

i use comments often. One problem I have faced is when using comments in a filtered list, the comment box ends up some distance from the data. Do you know a solution to this?

 

[KenWright]

Run this routine and it will rewrite them all back next to their cells:-

Sub RewriteComments()
Application.ScreenUpdating = False
Dim c As Range, s As String, r As Range
Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeComments)
For Each c In r
s = c.NoteText
c.ClearComments
c.NoteText s
c.Comment.Visible = False
c.Comment.Shape.TextFrame.AutoSize = True
Next c
Application.ScreenUpdating = True
End Sub

Regards
Ken..............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[harleynana]

this looks like having to program excel, i am not that familiar with excel, is there an easier way to filter with comments than the one you sent......

 

[KenWright]

Not that I know of, but this is so much easier than you think. If it helps, do the following (This is all one time stuff only):-

Open a new book, do Tools / Macro / Record Macro, and when prompted for where you want to save it, choose 'Personal macro Workbook' and hit OK.

Type a 1 anywhere and then hit the 'Stop recording' button that will have appeared.

Hit ALT+F11 together, and this will open a new window called the VBE (Visual Basic Editor). You should now see something like this:-

Top left you will hopefully see an explorer style pane. Within this pane you need to search for
your workbook's name, and when you find it you may need to click on the + to expand it. Within
that you should see the following:-

VBAProject(Personal.xls)
Microsoft Excel Objects
Sheet1(Sheet1)
ThisWorkbook
Modules
Module1

Double-Click Module1, and you should now see a white area open up with a piece of code it in like this:-

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 31/10/2003 by Ken Wright
'

'
ActiveCell.FormulaR1C1 = "1"
Range("E10&quot.Select
End Sub

Now simply copy the code I put in the first note, select all the code above, and then just paste, which should get rid of the code above and replace it with what I gave you.

Now just do File / Save Personal.xls
Then do File / Close and return to Microsoft Excel

You now have that stored in your personal macro workbook, and now whenever you need to move any comments, ie with your other file open and your comments all over the place, simply do Tools / Macro / Macros / RewriteComments and it should do it automatically.

Regards
Ken.................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------