Can I add a new row to my table using a macro whilst my table is filtered?

[Jeevz_87]

Hi All,


I'm having an issue with one of my spreadsheets where if I apply a filter to my table and then attempt to add a new row using a macro, I get a run time error which I cant seem to get passed!

Note: the spreadsheet must be able to add a new row whilst the table is filtered (the table gets shorter depending on the values being filtered)

I'm still new to using VBA but this is the code I'm currently using which copies a blank row with all the formatting in place and pastes as a new row to the bottom of the table;

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("Master Input").Range("A2:HS2").Copy rw



End Sub

Is there a way to do add a new row whilst filters have been applied in this way?

Thanks

J

 

[Alex Blakenburg]

Give this a try:

Sub test()

    Dim nextRow As Long
    Dim firstCol As Long
    
    With Worksheets("Master Input").ListObjects(1)
        nextRow = .Range.Row + .Range.Rows.Count
        firstCol = .Range.Column
    End With
    
    Sheets("Master Input").Range("A2:HS2").Copy _
            Sheets("Master Input").Cells(nextRow, firstCol)

End Sub

 

[Jeevz_87]

Hi Alex,


This worked great for the first time but then it didn't seem to work after that for some reason - not sure what happened!
It is close though as I filtered the table to a specific value and it did copy the row at A2:HS2 to the bottom of the table.

Is there a way for the code to find the last row in the table and paste the row there?

Thanks,

J

 

[Alex Blakenburg]

It is finding the last row + 1 of the table and relying on the table autoexpand.
What happens on the 2nd pass ?

 

[Jeevz_87]

Thats the strange part, nothing appears.

I can see that the macro is pointed at the right sheet and it focuses on the last row but when I execute, it doesn't seem to return anything.

Sub Add_new_row_4()



Dim nextRow As Long
Dim firstCol As Long



With Worksheets("Master Input").ListObjects(1)
nextRow = .Range.Row + .Range.Rows.Count
firstCol = .Range.Column
End With

Sheets("Master Input").Range("A2:HS2").Copy _
Sheets("Master Input").Cells(nextRow, firstCol)


End Sub

 

[Jeevz_87]

I've just noticed something else, the formula you gave pastes the row beyond the extent of the table before it stops working.

I guess that the problem has to do with the rows not being added to the table itself ("Master_Input"), so the size of the table doesn't expand with the addition of a new row and the macro stops working.

As soon as I delete that newly added row, the macro works again, I can add a row before it stops working.

Have I isolated the issue?

 

[Alex Blakenburg]

What is the macro supposed to do ? I mean it doesn't seem to make sense to keep copying A2:HS2 to the bottom.
Are you fitting it into a bigger macro ?

Ideally I would add code to reapply the filter after adding the data. If that data is then filtered out then the code would give the impression it has not added the rows but they would get hidden by the filter. Is that possibly what is happening ?

 

[Fluff]

You're code works fin for me. What error do you get & is the sheet protected?

 

[Jeevz_87]

What is the macro supposed to do ? I mean it doesn't seem to make sense to keep copying A2:HS2 to the bottom.
Are you fitting it into a bigger macro ?

Ideally I would add code to reapply the filter after adding the data. If that data is then filtered out then the code would give the impression it has not added the rows but they would get hidden by the filter. Is that possibly what is happening ?

Hi Alex and also Happy New Year everyone!

So, all I want the macro to do is to copy a row (row 2 which is a blank row that has all the formatting and formulas set up hidden at the top of the page) and paste it to the bottom of a table which may have multiple column filters in place from time to time.

I don't think its to do with the data being filtered out because I've checked the table when it's unfiltered and nothing appears to be happening.

The whole point of adding a new row to the filtered table is to allow the user the ability to input data without having to reset the view every time. It's a requirement because some users like to reference other projects that share the same office category for example to help them with their data entry.

The sheet has 3 macros, one which hides rows with a specific category selection and another which does a simple sort by duration.