Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Delete the bottom 3 rows in a table
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Delete the bottom 3 rows in a table
Resolved · High Priority · Version 2000
Reply Wed 2 Feb 2011, 20:46Delegate Davinder said...
Davinder has attended:
Excel VBA Intro Intermediate course
Delete the bottom 3 rows in a table
Hello,
I have a spreadsheet with a number of fields of data in a tabular format. New entries are constantly added to the bottom of this table increasing the total number of rows in the table. I need to create a macro in VBA which automatically identifies the bottom 3 rows in the spreadsheet and then deletes them. I understand that I need to identify the bottom 3 rows of the range of data and then use a delete command to remove these rows but I am not sure of the code that I need to use. Please can you advise how I go about creating the code to do this.
Thank you.
For upcoming training course dates see: Pricing & availability
RE: Delete the bottom 3 rows in a table
Hi Davinder,
Thank you for your question.
Try this bit of code on some test data first and make sure it is doing exactly what you wanted. It will prompt and ask you how many rows need to be deleted. The default has been set to three relating to your request.
Sub TrimAllSheets()
Dim cs As String
cs = ActiveSheet.Name
Dim y As Integer
y = Application.InputBox("How many bottom rows do you wish to delete?", _
Default:=3, Type:=1) 'Change default number (3) if desired.
If MsgBox("Are you sure you wish to delete " & y & " rows from the bottom of ALL sheets?", _
vbYesNo, "Trim ALL Sheets") = vbNo Then Exit Sub
Application.ScreenUpdating = False
Dim r As Range, s As Range
Dim ws As Worksheet
On Error Resume Next 'Error handler
For Each ws In ThisWorkbook.Worksheets
ws.Activate
Set r = ActiveSheet.Range("A65536").End(xlUp).Offset(-y + 1)
Set s = ActiveSheet.Range("A65536").End(xlUp)
If ActiveCell.Row < 10 Then GoTo circumv 'Not to delete Headers
Range(r, s).EntireRow.Delete
circumv:
Next ws
Sheets(cs).Activate
Application.ScreenUpdating = True
End Sub
I hope this answers your question.
Let me know.
Regards
Simon
Reply Thu 3 Feb 2011, 19:17
Edited on Thu 3 Feb 2011, 19:20Delegate Davinder said...
RE: Delete the bottom 3 rows in a table
Hi Simon,
Thanks for this, this is great however this solution takes it one step further than I need to go. I don't want it to prompt me to ask me how many rows to delete, I just want it to remove the bottom 3 lines each time as soon as I run the macro. I like the idea that it prompts you to check whether you really want to delete the rows but if you could confirm how the code needs to be amended so it always removes just the bottom 3 lines that would be great!
I also just need it to remove the bottom 3 rows in sheet 1 only and none of the other sheets.
Many thanks,
Dav
RE: Delete the bottom 3 rows in a table
Hi Davinder,
Just a quick addition to the last email the code deletes the last three rows from every sheet in the workbook so you can adapt the code and remove the references relating to the looping procedure that loops through all the sheets.
Regards
Simon
RE: Delete the bottom 3 rows in a table
Hi Davinda,
Try this code.
Sorry for the delay but work has been manic.
-----------------------------------------------------------
Sub DeleteLastThreeRows()
Sheets("Sheet1").Select
Cells(Rows.Count, ActiveCell.Column).End(xlUp).Offset(-2).Resize(3).EntireRow.Select
Selection.Delete
End Sub
----------------------------------------------------------
When you run the macro make sure the sheet1 is active and you must be in a cell in the data.
Test it first on some sample data.
Regards
Simon
Reply Tue 8 Feb 2011, 19:20Delegate Davinder said...
RE: Delete the bottom 3 rows in a table
Hi Simon this is good - but is there anyway I can get this to work without having to select a cell that is apart of the table range. I.e. I want the code to automatically identify where the data is and remove the last 3 lines.
Thanks.
|