VBA to Table Rows

happyhungarian

Board Regular
Joined
Jul 19, 2011
Messages
218
Hi, I'm using this code to find the last 39 rows of a Table and delete them. It works but the only problem is that I would prefer it to delete ONLY the rows from the table; not the entire row from the worksheet. Any easy tweaks to help this?

Sub DeleteTasktoTable()
Dim LC As Long
Dim LR As Long




LC = Cells(2, Columns.Count).End(xlToLeft).Column
LR = Cells(Rows.Count, LC).End(xlUp).Row


Range(Cells(LR - 38, LC), Cells(LR, LC)).EntireRow.Delete Shift:=xlUp


End Sub
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Try this
Reference:https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables

Code:
Sub ResetTable()


    Dim tbl As ListObject
    
    Set tbl = ActiveSheet.ListObjects("Table1")
    
    'Delete all table rows except first row
    With tbl.DataBodyRange
        If .Rows.Count > 1 Then
            .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
        End If
    End With
    
    'Clear out data from first table row
    tbl.DataBodyRange.Rows(1).ClearContents


End Sub
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,575
Office Version
  1. 365
Platform
  1. Windows
Maybe this:
Code:
Range(Cells(LR - 38, LC), Cells(LR, LC)).Delete Shift:=xlUp
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,991
Messages
5,526,098
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top