Adding a new row to a list when filter is applied

**Macster** · 05-14-2009, 03:02 AM

I'm stuck... I'm attaching a dummy worksheet.

If filtering column C, just showing 'A's, by pressing the add row button, I want to copy the last cell in column C and pasting it in row 73, since it's the next unused cell when the worksheet is unfiltered.

Can it be done?

Thanks in advance
Mac

**antoka05** · 05-14-2009, 03:39 AM

Try with this macro:

Sub Macro1()
   With ThisWorkbook.Sheets("sheet1")
      firstFreeRow = .Range("a1").CurrentRegion.Rows.Count + 1
   
      areasCount = .Range("c2:c" & firstFreeRow - 1).SpecialCells(xlCellTypeVisible). _
                   Areas.Count
   
      aAddress = Split(.Range("c2:c" & firstFreeRow - 1).SpecialCells(xlCellTypeVisible) _
                 .Areas(areasCount).Address(False, False), ":")
      
      lastFilteredRow = Range(aAddress(UBound(aAddress))).Row
      
      'to copy data you can write:
      .Cells(lastFilteredRow, "c").Copy .Cells(firstFreeRow, "c")
   End With
End Sub

Regards,
Antonio