Attempting to step through columns in a search

Question

I am attempting to create a module in Excel 2016 that will scan through a sheet and auto size any comments found. My current code requires me to adjust the Column Letter each time I run it. I am looking for a method to step through the columns in my loop. My current code is listed below and I am thanking anyone ahead of time for any assistance I can get. My current sheet only uses columns A through P.

Sub cmtsize()
    ActiveSheet.Unprotect pswd

    Range("a7:I7").Select
    lrow = Cells(Rows.Count, 1).End(xlUp).Row

    For xrow = 7 To lrow
        xcell = "c" & lrow
        Range(xcell).Select
        If ActiveCell.Comment Is Nothing Then
            GoTo nxt
        Else
            With Range(xcell).Comment.Shape
                .TextFrame.AutoSize = True
            End With
nxt:
        End If
    Next xrow

    ActiveSheet.Protect pswd
    Range("A6").Select
    MsgBox "Finished!"
End Sub

It looks like you are using the cells in column **C**. What columns do you want to loop over?? — Gary's Student, Sep 05 '18 at 14:35
look at using `OFFSET` so another loop say xcol and then `Range(xcell).offset(0,xcol)` where xcol will be like 1=A,2=B,3=C and so on. — Nathan_Sav, Sep 05 '18 at 14:40
Just a note, but I recommend to read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Also avoid `GoTo`, instead negate your `If` statement with `Not` like `If Not ActiveCell.Comment Is Nothing Then {do autosize} End If` — Pᴇʜ, Sep 05 '18 at 14:46

JosephC · Answer 1 · 2018-09-05T14:48:41.003

This will resize all comments on the specified worksheet. [Update] included option for password protected sheets. As well as the Finished Msgbox.

Sub test()
    Call ResizeComments(Sheet1)
    MsgBox ("Finished!")
End Sub

Private Sub ResizeComments(ByVal ws As Worksheet, Optional ByVal Pass As String = "")
    If Pass <> "" Then ws.Unprotect Pass

    Dim oComment As Comment
    For Each oComment In ws.Comments
        oComment.Shape.TextFrame.AutoSize = True
    Next

    If Pass <> "" Then ws.Protect Pass
End Sub

Attempting to step through columns in a search

1 Answers1