Delete Unselected Rows of a Table

[MrBartlett]

Hi all,

I've been referencing this thread: https://www.mrexcel.com/forum/excel-...cted-rows.html

Specifically, this formula:

 	Sub KeepSelectedRows()
With Selection
OnErrorResumeNext
    Rows("1:" & .Row - 1).Delete
    Rows(.Row + .Rows.Count & ":" & Rows.Count).Delete
OnErrorGoTo 0
EndWith
EndSub

However, I just want to delete rows of a table, not entire rows of a sheet. I've been looking through Google for at least a good hour, and I can't find a solution for the exact use I have in mind.

Basically, I have a macro that pastes data in a table. But, in some cases, it may be updating a table that already has data, with more rows than are being pasted. For example, a table of 400 rows already has data in A1:A401. My macro pastes 300 rows in A1:A301. I want to delete those extra 100 rows that were not pasted/were already there. However, I just want to delete the table rows, not the sheet rows. Also ,when looking at the sheet after the macro pastes the data, it still has that pasted data selected. So, I figured a script that could remove all table rows underneath (if there is data underneath) would be the most elegant solution.

I've tried various formulas, even clearing table contents and resizing the table before the data is pasted. But, it slows everything down and usually ends up not loading.

Thanks in advance for any insights

 

[My Aswer Is This]

I suggest we delete the Table DatabodyRange
And then paste in your new data like this
Modify Table name to meet your needs.

I have my script copying my selected Range

You will have to modify this also

Sub Clear_Table_Rows()
'Modified  7/22/2018  9:30:23 PM  EDT
With ActiveSheet.ListObjects("Table1").DataBodyRange
.Rows.Delete
Selection.Copy .Cells(1, 1)
End With
End Sub

 

[MrBartlett]

I suggest we delete the Table DatabodyRange
And then paste in your new data like this
Modify Table name to meet your needs.

I have my script copying my selected Range

You will have to modify this also

Sub Clear_Table_Rows()
'Modified  7/22/2018  9:30:23 PM  EDT
With ActiveSheet.ListObjects("Table1").DataBodyRange
.Rows.Delete
Selection.Copy .Cells(1, 1)
End With
End Sub

Thanks for the reply. I tried something like that, but it basically hangs when trying to delete the data with VBA. Or, if it doesn't actually hang, then it certainly takes longer than the 3 minutes that I wait. Manually, it takes like 10 seconds to delete all of the table rows.

The same thing happens when I use the script you provided.

My table is 12000 rows by 16 columns.

 

[My Aswer Is This]

Well I do not know what to tell us.
I just tested this on about twenty rows.
If your looking for absolute speed then I'm not the man to help you.
You never mentioned having 12,000 rows

But I'm sure someone else here at Mr. Excel will be able to help you.

 

[sirN]

Using your sub, how can I delete all rows except for the first row (not the header, the first one with data). The first row has a formula in it and I want to keep it. When I copied and ran your Sub, it worked great, except it deletes the first row as well.

 

[My Aswer Is This]

edit

 

[My Aswer Is This]

SirN
Try this:

Sub Clear_Table_Rows()
'Modified  7/22/2018  11:18:37 PM  EDT
Application.ScreenUpdating = False
Dim ans As Long
    With ActiveSheet.ListObjects("Table1").DataBodyRange
        ans = .Rows.Count
        .Offset(1).Resize(ans - 1).Rows.Delete
    End With
Application.ScreenUpdating = True
End Sub

 

[MrBartlett]

Well I do not know what to tell us.
I just tested this on about twenty rows.
If your looking for absolute speed then I'm not the man to help you.
You never mentioned having 12,000 rows

But I'm sure someone else here at Mr. Excel will be able to help you.

Haha, yes I guess 12,000 is a lot. Thanks for your help thus far!

Hopefully someone can come around with a script that can accommodate my scenario.

 

[My Aswer Is This]

Haha, yes I guess 12,000 is a lot. Thanks for your help thus far!

Hopefully someone can come around with a script that can accommodate my scenario.

Yes there may be someone who knows how to do this in seconds.
Fast is a relative term
Some people say 3 seconds is slow and others say .2 milliseconds is slow.

 

[My Aswer Is This]

You can try this:
I never time my scripts but this may be fast.

Sub Filter_Me_Please()
'Modified  7/22/2018  11:51:55 PM  EDT
Application.ScreenUpdating = False
With ActiveSheet.ListObjects("Table1").DataBodyRange
    .AutoFilter 1
    ans = .Rows.Count
    If ans > 1 Then
    .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Delete
    End If
    .AutoFilter
End With
Application.ScreenUpdating = True
End Sub