vba to delete last row from a table

[dpaton05]

What is the vba code I can add to a button to delete last row from a table?

 

[Fazza]

Re: Deleting cells from the bottom of a table

you seem to have two current threads on the front page of the forum right now about this very question

I also don't know why???

 

[My Aswer Is This]

Re: Deleting cells from the bottom of a table

Or try this with a If Statement:

Private Sub CommandButton2_Click()
'Modified  8/30/2018  7:36:35 PM  EDT
   Dim ans As Long
    With ActiveSheet.ListObjects("npss_quote").DataBodyRange
        ans = .Rows.Count
        If ans > 1 Then .Rows(ans).Delete
    
    End With
End Sub

 

[dpaton05]

Re: Deleting cells from the bottom of a table

I made a mistake then couldn't delete it

 

[dpaton05]

Re: Deleting cells from the bottom of a table

Problem is that it will delete all the rows in your table if you let it, including all your formulas etc. How do I make it so all the rows can be deleted except the first row, but if you press delete when there is only one row left, it will just clear the row of data and not formulas?

 

[My Aswer Is This]

Re: Deleting cells from the bottom of a table

If you use my second script it will not let you delete first row in Table.

 

[Fazza]

Re: Deleting cells from the bottom of a table

I made a mistake then couldn't delete it

OK. Just ask a mod to help. cheers

 

[dpaton05]

Re: Deleting cells from the bottom of a table

If you use my second script it will not let you delete first row in Table.

That works even better then the other one

 

[My Aswer Is This]

Re: Deleting cells from the bottom of a table

If you want to clear contents of Row(1) after all other rows are deleted
Try this:

Private Sub CommandButton2_Click()
'Modified  8/30/2018  8:21:30 PM  EDT
   Dim ans As Long
    With ActiveSheet.ListObjects("npss_quote").DataBodyRange
        ans = .Rows.Count
        If ans > 1 Then .Rows(ans).Delete
        If ans = 1 Then .Rows(1).ClearContents
    End With
End Sub

 

[dpaton05]

Re: Deleting cells from the bottom of a table

If you want to clear contents of Row(1) after all other rows are deleted
Try this:

Private Sub CommandButton2_Click()
'Modified  8/30/2018  8:21:30 PM  EDT
   Dim ans As Long
    With ActiveSheet.ListObjects("npss_quote").DataBodyRange
        ans = .Rows.Count
        If ans > 1 Then .Rows(ans).Delete
        If ans = 1 Then .Rows(1).ClearContents
    End With
End Sub

That code deleted the top row formulas.

 

[My Aswer Is This]

Re: Deleting cells from the bottom of a table

My mistake.
Try this:

Private Sub CommandButton2_Click()
'Modified  8/30/2018  9:24:30 PM  EDT
   Dim ans As Long
    With ActiveSheet.ListObjects("npss_quote").DataBodyRange
        ans = .Rows.Count
        If ans > 1 Then .Rows(ans).Delete
        If ans = 1 Then .Rows(1).Cells.SpecialCells(xlCellTypeConstants).ClearContents
    End With
End Sub