My problem is illustrated in the PrintScreen below:
I've searched a lot on the Internet and almost all sources say to delete the empty rows at the end of the sheet and save the document. For example that is what is suggested here (see Section 2f).
Sometimes this works, but most of the time it doesn't!
I don't know if this is relevant information or not, but my Excel document contains three user-defined macros, including two that have to do with comments formatting. There are many comments in the sheet "Notes A", most of them with pictures as backgrounds. Also, my document is not protected, so I do not think the problem is that the extra lines are locked.
Is there a definitive solution to this?
Added: Here are the codes of my three macros.
Module 1: This is a macro that sorts alphabetically the string of letters of a word. For example, applying it to a cell containing HELLO will give EHLLO as the output.
Option Compare Text
Function SortString(ByVal iRange, Optional Croissant As Boolean = True)
'Trevor Shuttleworth, mpep, modifié fs, mpfe
Dim i%, j%, sTemp$
For j = 1 To Len(iRange) - 1
For i = 1 To Len(iRange) - 1
If Mid(iRange, i, 1) > Mid(iRange, i + 1, 1) Then
sTemp = Mid(iRange, i, 1)
Mid(iRange, i, 1) = Mid(iRange, i + 1, 1)
Mid(iRange, i + 1, 1) = sTemp
End If
Next
Next
If Croissant = False Then
For i = Len(iRange) To 1 Step -1
SortString = SortString & Mid(iRange, i, 1)
Next
Exit Function
End If
SortString = iRange
End Function
Module 2: This is a macro that automatically resizes comments containing words.
Sub Fitrangecomments()
'Updateby20140325
Dim rng As Range
Dim WorkRng As Range
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each rng In WorkRng
If Not rng.Comment Is Nothing Then
rng.Comment.Shape.TextFrame.AutoSize = True
End If
Next
End Sub
Module 3: This is a macro that asks the user to choose a background image for a to-be-created comment and that automatically resizes the comment to match the image dimensions.
Sub InsertComment()
Dim strImagePath As Variant
Dim objImage As Object
strImagePath = Application.GetOpenFilename("Picture, *.jpg; *.png; *.bmp")
If strImagePath = False Then Exit Sub
Set objImage = CreateObject("WIA.ImageFile")
objImage.LoadFile strImagePath
With ActiveCell
If .Comment Is Nothing Then .AddComment ("")
.Comment.Shape.Fill.UserPicture strImagePath
.Comment.Shape.Height = objImage.Height * 0.75
.Comment.Shape.Width = objImage.Width * 0.75
End With
End Sub