I copied and replaced your code (below) with everything above Sub
Comments_AutoSize() but I'm afraid it still doesn't work but I'm not going to
waste any more of your valuable time. I'll live with it - maybe someday I
learn code and fix it myself but thanks so much for your time and patience.

Option Explicit
Sub ResetComments()

Dim cmt As Comment

If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If

For Each cmt In ActiveSheet.Comments
cmt.Shape.Top = cmt.Parent.Top + 5
cmt.Shape.Left = _
cmt.Parent.Offset(0, 1).Left + 5
Next cmt

End Sub
Sub Comments_AutoSize()
'posted by Dana DeLouis 2000-09-16
Dim MyComments As Comment
Dim lArea As Long
For Each MyComments In ActiveSheet.Comments
With MyComments
.Shape.TextFrame.AutoSize = True
If .Shape.Width 300 Then
lArea = .Shape.Width * .Shape.Height
.Shape.Width = 200
' An adjustment factor of 1.1 seems to work ok.
.Shape.Height = (lArea / 200) * 1.1
End If
End With

"Dave Peterson" wrote:

I meant before the "for/next" group of statements (or just before the "For Each"
line):

Option Explicit
Sub ResetComments()

Dim cmt As Comment

If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If

For Each cmt In ActiveSheet.Comments
cmt.Shape.Top = cmt.Parent.Top + 5
cmt.Shape.Left = _
cmt.Parent.Offset(0, 1).Left + 5
Next cmt

End Sub



piddilin wrote:

Still doesn't work, but you said "If you want, you could add this section
before the "for next" line:" and I couldn't find the "for next" line so put
it before the "Next" like, as follows:

Sub ResetComments()
Dim cmt As Comment
For Each cmt In ActiveSheet.Comments
cmt.Shape.Top = cmt.Parent.Top + 5
cmt.Shape.Left = _
cmt.Parent.Offset(0, 1).Left + 5
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
Next
End Sub
Sub Comments_AutoSize()
'posted by Dana DeLouis 2000-09-16
Dim MyComments As Comment
Dim lArea As Long
For Each MyComments In ActiveSheet.Comments
With MyComments
.Shape.TextFrame.AutoSize = True
If .Shape.Width 300 Then
lArea = .Shape.Width * .Shape.Height
.Shape.Width = 200
' An adjustment factor of 1.1 seems to work ok.
.Shape.Height = (lArea / 200) * 1.1
End If
End With
Next ' comment
End Sub

"Dave Peterson" wrote:

I made a mistake in a different post and the same mistake he

If ActiveSheet.AutoFilterMode Then
ActiveSheet.ShowAllData
End If

should be

If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If

Dave Peterson wrote:

I think the simplest solution would be to only run the macro when all the rows
are visible.

You could even show all the rows in the macro, but then you might be screwing up
the filter.

If you want, you could add this section before the "for next" line:

If ActiveSheet.AutoFilterMode Then
ActiveSheet.ShowAllData
End If

====
An alternative may be to just fix the comment position of the cells that are
visible???

piddilin wrote:

Wow! I created two macros (ResetComments and ResizeComments) and they are
awesome! Only problem is ResetComments macro doesn't work if I click the
AutoFilter button in a column, select a specific item from the drop down and
Right-clicking to Edit Comment creates - I still get the long pointer and see
the comment elsewhere on the worksheet. Any fix for that?

PREVIOUS DISCUSSION copied he
Put them back where they belong with a macro???

From Debra Dalgleish's site:
http://www.contextures.com/xlcomments03.html#Reset

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

gillilal wrote:

comments do not move with the cell they were created on and has long pointers
to them in the worksheet. They are stationary and not floating with the cell
if moved. So the comment may be several pages away from its intended
location. Any suggestions?

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson


--

Dave Peterson