0

I have a table which can vary in range (rows only) depending on the numbers of inputs (rows).

I've written a code to transfer the data from the table to my data base. That's working fine.

But, at the end of this script, I'd like to delete the table rows, expect the first and the second rows, where respectively, my headers, and my formula lives.

Bearing in mind that the total number of rows can vary, how can I delete all the table rows but the 2 first one?

FYI

The table range (headres and first row) is A18:D19

    Dim Cl As Range
For Each Cl In Sheets("Data Entry").Range("A19:A1000")
    If Cl.Value = "" Then Exit For 'Exits on first empty cell
    Sheets("Source Ingredients").Range(Cl.Value).Value = Cl.Offset(, 2).Value

Thanks in advance Greg

BigBen
  • 46,229
  • 7
  • 24
  • 40
Greg
  • 19
  • 6
  • `Sheets("Data Entry").Range("A20:D10000").Clear`? Or maybe find the last row and then clear instead of hard-coding it? – BigBen Jul 15 '20 at 02:17

1 Answers1

1

This works, where Table1 is the name of the table. If you aren't sure how to find this, click in the table, then select Table Design in the banner at the top, and the table name is on the top left under Properties.

Application.Range("Table1").ListObject.DataBodyRange.Offset(1, 0).Resize(Application.Range("Table1").ListObject.DataBodyRange.Rows.Count - 1, _
    Application.Range("Table1").ListObject.DataBodyRange.Columns.Count).Rows.Delete

With the address abbreviated using a With statement:

With Application.Range("Table1").ListObject.DataBodyRange
    .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
End With
TerrifiedJelly
  • 135
  • 10