Deleting n number of lines from the bottom of a table

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,352
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have code to add n lines to my table but only have a button to delete one line at a time from the bottom. How do I change the sub DeleteRowBottom so you are asked, similar to the AddLines feature, how many lines to delete from the bottom of the table?

My AddLInes code is:
VBA Code:
Sub AddLines()
    Application.EnableEvents = False
        Dim ws As Worksheet, x As Long, tbl As ListObject, n As Long
       On Error GoTo cancelled:
       n = InputBox("How many lines would you like to add?")
        Set ws = ActiveSheet
        Set tbl = ws.ListObjects("CSS_quote")
        For x = 1 To n
            'add a row at the end of the table
            tbl.ListRows.Add
        Next x
        Range("A:A").NumberFormat = "dd/mm/yyyy"
    tbl.Range.Cells(tbl.ListRows.Count - n + 2, 1).Select
    Application.EnableEvents = True
cancelled:
    Exit Sub
End Sub

My DeleteRowBottom code is:
VBA Code:
Sub DeleteRowBottom()

    Quoting.Unprotect Password:=ToUnlock
        Dim ans As Long
        On Error GoTo Halt
        With ActiveSheet.ListObjects("CSS_quote").DataBodyRange
            ans = .Rows.Count
            If ans = 0 Then Exit Sub
            If ans > 1 Then .Rows(ans).Delete
            If ans = 1 Then .Rows(1).Cells.SpecialCells(xlCellTypeConstants).ClearContents
        End With
        'Selection.ListObject.ListRows(6).Delete
        Call InsertFormulas
Halt:
    On Error GoTo 0
    'ActiveSheet.Protect Password:="CSSadmin"
    Application.EnableEvents = True
End Sub
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
opps misunderstood
 
Last edited:
Upvote 0
Thanks for the reply zot. That deletes them from somewhere I think but it is not from the bottom as I added some blank rows at the bottom, ran the sub but the blank rows were not deleted.
 
Upvote 0
What I want to do is add an input box to ask how many rows you want to delete from the bottom.
 
Upvote 0
Thanks for the reply zot. That deletes them from somewhere I think but it is not from the bottom as I added some blank rows at the bottom, ran the sub but the blank rows were not deleted.
I was late to retract. I need to understand more. You want to specify how many rows from bottom? Then you need to find last row,
 
Upvote 0
How you sheet looks like? Can I use column A as reference to find last row like LastRow=Range("A1").End(xlDown) ?

Oh.. we are dealing with list object. my mistake
 
Last edited:
Upvote 0
Ok, so my add lines code in post 1, asks the user how many lines they want added. These are added to the bottom of the table. I want a similar feature for the sub to delete rows.

Currently, the sub DeleteRowBottom, in post 1, deletes a row at a time from the bottom of the table, CSS_quote. I want to ask the user how many lines they want deleted. These are to be from the bottom of my table.

This is my table with one line.
CSS_quoting_tool_33.39.xlsm
ABCEFGHIJKLMNO
10DateServiceUnit PriceHoursStaff Req.Kms TravelledPrice ex. GSTRateTransport $MaxPayColumn3ActivitiesColumn1Column2
1106/07/2020Tutoring$73.1011$73.10$73.10$0.00$73.100 
CSS_quote_sheet
Cell Formulas
RangeFormula
C11C11=IF([@Service]="Activities",[@Activities],INDEX(Service_Types,MATCH([@Service],Sheet2!$A$5:$A$12,0),MATCH([@[Day rate]],Sheet2!$A$5:$E$5,0)))
H11H11=IF([@Service]="Activities",ROUNDDOWN([@Activities]+[@[Transport $]],2),IF([@Service]="Carer Respite",[@[Staff Req.]]*[@Rate],ROUNDDOWN(((IF(OR(ISBLANK(A11),ISBLANK(D11),ISBLANK(B11)),0,[@[Transport $]]+[@MaxPay]))*[@[Staff Req.]]),2)))
I11I11=INDEX(Sheet2!$A$5:$E$12,MATCH([Service],Sheet2!$A$5:$A$12,0),MATCH([Day rate],Sheet2!$A$5:$E$5,0))
J11J11=([@[Kms Travelled]]*1.22)
K11K11=[Rate]*[Hours]
N11N11=IF(B11="Supervised transport",1,0)
O11O11=IF(B11="Supervised transport",1,0)
 
Upvote 0
I tried to write this code but it deletes rows from the top
VBA Code:
Sub DeleteRowBottom()
    Application.EnableEvents = False
    'ActiveSheet.Unprotect Password:="CSSadmin"
        Dim ws As Worksheet, x As Long, tbl As ListObject, n As Long, ans As Long
       On Error GoTo cancelled:

       n = InputBox("How many lines would you like to Delete?")
       
        For x = 1 To n
            
            With ActiveSheet.ListObjects("CSS_quote").DataBodyRange
                ans = .Rows.Count
                If ans = 0 Then Exit Sub
                If ans > 1 Then .Rows(n).Delete
                If ans = 1 Then .Rows(n).Cells.SpecialCells(xlCellTypeConstants).ClearContents
            End With
        Next x
        
        Set ws = ActiveSheet
        Set tbl = ws.ListObjects("CSS_quote")
        For x = 1 To n
            'delete a row at the end of the table
            tbl.ListRows.Delete
        Next x
        Range("A:A").NumberFormat = "dd/mm/yyyy"
    tbl.Range.Cells(tbl.ListRows.Count - n + 2, 1).Select
    
Application.EnableEvents = True
cancelled:
    Exit Sub

End Sub
 
Upvote 0
Will this works

VBA Code:
Sub DeleteRowBottom()

    Quoting.Unprotect Password:=ToUnlock
        Dim ans As Long, n As Long
        On Error GoTo Halt
        With ActiveSheet.ListObjects("CSS_quote").DataBodyRange
            n = InputBox("How many lines would you like to delete?")
            ans = .Rows.Count
            If ans = 0 Then Exit Sub
            If ans > 1 Then
                For n = 1 To n
                    .Rows(ans).EntireRow.Delete
                    ans = .Rows.Count
                Next
            End If
            If ans = 1 Then .Rows(1).Cells.SpecialCells(xlCellTypeConstants).ClearContents
        End With
        'Selection.ListObject.ListRows(6).Delete
        Call InsertFormulas
Halt:
    On Error GoTo 0
    'ActiveSheet.Protect Password:="CSSadmin"
    Application.EnableEvents = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top