0

I've been wracking my brain trying to solve this issue, and I bet it's super simple, but the answer is eluding me!

I'm trying to add a new row to a filtered table in Excel via a macro button. The macro works when the table is unfiltered but, due to client requirements, they want to add rows regardless of how filtered the table is. The user uses the column header dropdowns to filter the table - sometimes three or filters applied at a time.

Here is the current VBA code I'm using;

Public Sub Add_new_row_2()
    Dim tbl As ListObject
    Dim rw As Range

    Set tbl = Sheets("Master Input").ListObjects(1)
    Set rw = tbl.ListRows.Add.Range
    
    Sheets("Data").Range("CF2:KX2").Copy rw
End Sub

The code copies a fully formatted blank row within a range (CF2:KX2) located on a separate tab and pastes the row to the bottom of the table. It works well whilst the table is unfiltered or sorted, but when the user tries to apply multiple column filters, they get a Runtime error "1004".

Is there a way to add a new row despite any heavy filtering?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
J_noob
  • 11
  • 1
  • which line of code exactly gives the error? – Máté Juhász Jan 05 '22 at 12:52
  • https://stackoverflow.com/questions/48628632/excel-vba-insert-row-in-table-when-filtered – SJR Jan 05 '22 at 13:09
  • @MátéJuhász, it appears to be the line that references the range. – J_noob Jan 05 '22 at 13:16
  • @SJR, yep i see where you're going with that but wanted to see if the new row is possible without having to clear the filters first. Is this possible? – J_noob Jan 05 '22 at 13:17
  • If you try it manually, the option to add a table row disappears (though you can add an entire sheet row) so I think the answer is probably 'no', and there is no reason really not to do as suggested in that question. – SJR Jan 05 '22 at 13:31
  • @SJR I actually managed to convince managers that clearing the filters first would be better and they agreed. How do I add a clear all filter clause into the VBa code above? – J_noob Jan 06 '22 at 11:02

0 Answers0