Clear the contents of last row

[dpaton05]

I have a table and buttons to add and delete rows. It won't delete the last row of the table when there is just one row left and this is good as if it did, it would delete all the formulas from it. However, I do want to clear it of contents when it gets down to deleting the last row, but keep the formulas and data validation lists. How do I do this?

I have the following code in my delete button:

Private Sub cmdDeleteRow_Click()

Dim ans As Long
With ActiveSheet.ListObjects("npss_quote").DataBodyRange
ans = .Rows.Count
If ans > 1 Then .Rows(ans).Delete

End With


End Sub

 

[My Aswer Is This]

I provided this code to you earlier. Not sure what your now asking for.
And normally you do not need to have a button to add rows to a table.

Enter a value in row 1 of a table and then a value in the next row and the Table automatically expands
Just don't skip rows.

And what type formula do you have in these rows?

Are you wanting to Sum all the values in the column or something like that?
It may help to understand your ultimate goal.

 

[My Aswer Is This]

And if your wanting to clear the last row of the Table like your subject title says. Why would not the script you have shown here work? It deletes the last row of the Table.

 

[dpaton05]

I provided this code to you earlier. Not sure what your now asking for.
And normally you do not need to have a button to add rows to a table.

Enter a value in row 1 of a table and then a value in the next row and the Table automatically expands
Just don't skip rows.

And what type formula do you have in these rows?

Are you wanting to Sum all the values in the column or something like that?
It may help to understand your ultimate goal.

The spreadsheet is used to generate quotes. Each line is a new item on the quote.

A17: Date
B17: Day of service, contains the following formula: =IF(A17="","",IF(COUNTIF(Sheet2!$G$85:$DO$95,A17),"Public Holiday",IF(WEEKDAY(A17,2)>5,"Weekend","Mon-Fri")))
Sheet2!$G$85:$DO$95 contains a list of public holidays for the next approximately 100 years and that is calculated starting from the current date.
C17: Data validation list
D17: Hours
E17: No. of workers required
F17: kms travelled
G17: Extra pickups that need to be done
H17: =(IF(OR(ISBLANK(A17),ISBLANK(B17),ISBLANK(C17),ISBLANK(D17)),0,[@[extra pickup]]+[@[$ 4 extra
hrs]]+[@[$ 4 kms]]+[@[Max
Pay]]))
I17-L17 are hidden table fields used for calulations
I17: =(INDEX(extra_pickup_location,MATCH([@[What service]],$P$4:$P$8,0),MATCH([@[Day of service]],$P$4:$S$4,0))*[@[Extra pickups,
up to 3 CYP]])
J17: =(INDEX(extra_hours_over_1,MATCH([@[What service]],$P$11:$P$17,0),MATCH([@[Day of service]],$P$11:$S$11,0))*IF(D17>1,D17-1,0))
K17: =IF([@[What service]]="Supervised Transport",IF([@Kms]<70,(INDEX(plus_per_km,MATCH([@[What service]],$V$11:$V$15,0),MATCH([@[Day of service]],$V$11:$Y$11,0))*70),(INDEX(plus_per_km,MATCH([@[What service]],$V$11:$V$15,0),MATCH([@[Day of service]],$V$11:$Y$11,0))*[@Kms])),(INDEX(plus_per_km,MATCH([@[What service]],$V$11:$V$15,0),MATCH([@[Day of service]],$V$11:$Y$11,0))*[@Kms]))
L17: =INDEX(Max_pay_1hr,MATCH([@[What service]],$W$4:$W$8,0),MATCH([@[Day of service]],$W$4:$Z$4,0))

These cells need to make reference to other tables within the spreadsheet that are used in calculations.

The spreadsheet appears to work with the add and delete row buttons but when I am deleting rows, it will delete them down to the last row then it won't delete any further. I know you said you told me how to do it but I think I remember that I tried that and it completely deleted everything in the last row. However, that may not of been the code you gave me but I would of predicted that if I used it and it worked, I would not of got rid of it. Could you let me know the code again please as I can't find the post where you told me the code?

 

[My Aswer Is This]

Try this:

[LEFT][COLOR=#333333][FONT=monospace]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[/FONT][/COLOR][/LEFT]

 

[dpaton05]

That's great thanks. If the button is clicked when the row is blank I get an error message saying "no cells found". How do I suppress that message so if it is clicked more?

 

[dpaton05]

Try this:

[LEFT][COLOR=#333333][FONT=monospace]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[/FONT][/COLOR][/LEFT]

With this code, what do I need to add so in addition to deleting rows from the bottom of the table, if I have a cell in a row of the table selected it will delete that row in the table?

 

[My Aswer Is This]

Delete the row selected in the table is:
This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[dpaton05]

I found out how to do this.

https://www.mrexcel.com/forum/excel-questions/683740-vba-delete-table-row-based-selection.html

 

[My Aswer Is This]

So Show me the entire script your using now.