June 12, 2011

Add a Row to an AutoFilter

Adding a row to the bottom of an AutoFilter is something we do often enough but doing it without code requires more clicks than I would like - copy an existing row, select the row below where the existing bottom row is, then opening up Paste Special. We probably want to paste both Formats and Formulas, so we have do one, reopen Paste Special and do the it again. Okay, things have improved slightly with Excel 2010, but even so...

So, let's use some VBA to help reduce some RSI.

Sub AddRowToFilter()

    On Error Resume Next

    With ActiveSheet.AutoFilter.Range

        .Rows(2).Copy

        With .Offset(.Rows.Count).Rows(1)
            .PasteSpecial xlPasteFormats
            .PasteSpecial xlFormulas
        End With

    End With

    Application.CutCopyMode = False

    If Err <> 0 Then MsgBox "This sheet has no filter"

    On Error GoTo 0
End Sub

Note the message box just in case there is no filter.

New Banner: Just in case you have not noticed, I've changed my site banner to a) show my true site address (andrewsexceltips.net), and b) show I'm still alive and kicking. I will miss Einstein poking out his tongue but hopefully the new and simple design will grow on you. See you next time I post!