Adding Comments II

In Adding Comments, I read the data from a text file and created comments with one of the fields because it was large. I mentioned in that post that the way I wouldn’t do it is to import the text file normally, then a run macro. I don’t think it’s necessarily a bad way, it’s just not my preference.

I start by importing the text file using File > Open. That brings up the Import Text wizard and results in a sheet like this:

Text file after importing into Excel

In a separate file, I have a macro. The separate file could be an add-in, for instance, but it doesn’t have to be. The code just can’t be in a text file.

Sub MoveColumnToComment()
   
    Dim ws As Worksheet
    Dim rCell As Range
    Dim rRng As Range
   
    Set ws = ActiveSheet
    Set rRng = Intersect(ws.Columns(1), ws.UsedRange)
   
    For Each rCell In rRng.Cells
        rCell.AddComment rCell.Offset(0, 1).Value
    Next rCell
   
    ws.Columns(2).Delete
   
End Sub

Once the code is run, I’m left with:

Excel range after macro showing comments added

Posted in Uncategorized

Leave a Reply

Your email address will not be published. Required fields are marked *