Hey all,

So I have this macro that moves data from one column to the top of another and removes data from the original. My scope has changed a bit and I was wondering if there was a way to tweak it so that it would only move filtered or selected items.

For example, my spreadsheet is roughly 1000 rows. I get comments from another team, but of the 100 or so comments that I need to move weekly 80 of them offer no real value so it's strictly me moving them. What I would like to do is filter for those 80 and move them leaving the 20 or so that I actually need to pay attention to.

Here's the code I am currently using, it works perfectly, just wondered if it could be better.

I am running Office 2007 and using xlsx files if that makes a difference.

Sub aComments2012()
'
' aComments2012 Macro
    Dim cell As Range
    Dim s As String
    Dim r As Range
    Dim sFrom As String
    Dim sTo As String
    Dim lFrom As Long
    Dim lOffset As Long
    Dim rRange As Range
    Dim rTop As Range
    Dim rBottom As Range
    Dim sh As Worksheet

    sFrom = "AM"
    sTo = "AK"

    On Error GoTo EF
    Application.EnableEvents = False

    lFrom = Range(sFrom & 1).Column
    lOffset = Range(sTo & 1).Column - lFrom

    Set sh = ActiveSheet
    With sh
        Set rTop = .Cells(2, lFrom)
        Set rBottom = rTop.Offset(.UsedRange.Rows.Count - 1)
        Set rRange = Range(rTop, rBottom)
    End With

    For Each cell In rRange
        With cell
            Set r = .Offset(0, lOffset)
            s = r.Value
            If InStr(s, .Value) = 0 Then
                If (s <> "") Then
        s = .Value & vbLf & s
        Else
        s = .Value
        End If
                With r
                    .Value = s
                    .VerticalAlignment = xlTop
                End With
                .Value = ""        'remove this line if you want column(sFrom) to remain as is.
            End If
        End With
    Next cell

EF:
    Application.EnableEvents = True
End Sub