VBA Express : Excel - Delete Bottom or Last Row(s) From All Sheets

Excel

Delete Bottom or Last Row(s) From All Sheets

Ease of Use

Intermediate

Version tested with

2000/XP

Submitted by:

Zack Barresse

Description:

Will delete a user-defined amount of rows from the bottom of all sheets. Bottom row (or last row) is classified as the last row in column A that contains any data.

Discussion:

For use in report generating, or clean-up of your workbook. You may have reports that have the same calculations on every sheet on the bottom three rows. Now, you need to get rid of all these rows of extra calculations. This code sees spaces as data, so be sure you don't have just spaces in any of your cells.

Code:

instructions for use

			Option Explicit 
 
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

How to use:

Copy the above code.
Open any workbook.
Press Alt + F11 to open the Visual Basic Editor (VBE).
From the Menu, choose Insert-Module.
Paste the code into the right-hand code window.
Close the VBE, save the file if desired.

Test the code:

Tools-Macro-Macros, and double-click TrimAllSheets.

Sample File:

trimAllTest.zip 8.45KB

Approved by mdmackillop

This entry has been viewed 298 times.