Results 1 to 1 of 1

Thread: How to extract cell comments of all sheets using VBA?

  1. #1

    How to extract cell comments of all sheets using VBA?

    Code:
    Sub GetComments()
    
        Dim cell As Range
        Dim counter As Long, k As Long
        Dim ComStr As String
        
        counter = 0
        For k = 1 To ThisWorkbook.Sheets.Count
            Application.StatusBar = k & " of " & ThisWorkbook.Sheets.Count & ".."
            For Each cell In ThisWorkbook.Sheets(k).UsedRange
                ComStr = ""
    
                On Error Resume Next
                ComStr = cell.Comment.Text
                On Error GoTo 0
    
                If ComStr <> "" Then
                    counter = counter + 1
    		'Sheet1 can be replaced by the sheet where the comments are to be extracted
                    Sheets("Sheet1").Cells(counter, 1).Value = Sheets(k).Name
                    Sheets("Sheet1").Cells(counter, 2).Value = cell.Row
                    Sheets("Sheet1").Cells(counter, 3).Value = cell.Column
                End If
    
            Next
        Next k
    
        Application.StatusBar = "Ready"
    
    End Sub
    Last edited by ExcelBaap; 07-26-2017 at 07:56 PM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •