How to delete last row?

[ggji90]

Hello everyone.
I want to ask some questions.
I have designed a macro that is deleting the last row:

Sub ft()
lastrow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Rows(lastrow).Delete
End Sub

When it is run it deletes the last row of the current excel sheet I am working on.

My question is how can I run this macro in any excel file present in my PC without re-writing it in each file?
And other question is how can I run this macro in a file without opening that file?

 

[JazzSP8]

For your first question try looking at Personal Workbooks;

Make A Personal Macro Workbook

Macros stored in the Personal Macro Workbook are always at your disposal. You can run the macros on any workbook that you open on the computer. Also, you do not have to jump through security hoops to run macros stored in the Personal Macro Workbook.

www.mrexcel.com

Not really sure what you mean by the second question, it'd be a bit dangerous to open every single Workbook you have on your PC and delete the last row.

You can write code that'll open up all Workbooks in a specific folder, execute your Macro and then save it again which might be the better option so you've got some control over which Workbooks get there last row deleted.

You can find a ton of examples of code that opens all workbooks in a folder and runs a macro, this thread for example;

Open all files in a folder, run a macro

I have 2,000 spreadsheets in a folder - I would like some code that; - opens each spreadsheet one at a time - in sheet "Summary", copy/paste special whole sheet - in sheet "Summary", columns C&D - I only want the first letter of any text to remain (converting names to initials) - delete all...

www.mrexcel.com

You'll need to tailor it to your specfic needs though.

 

[trunten]

I was thinking something like this for you:

Option Explicit

Sub deleteLastRowInWorkbookSheets()
    Dim a, files, fn, sht As Worksheet, thisWb As Workbook, wb As Workbook
    
    files = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", MultiSelect:=True)
    If TypeName(files) = "Boolean" Then Exit Sub
    If Not IsArray(files) Then files = Array(files)
    Set thisWb = ActiveWorkbook
    For Each fn In files
        Application.ScreenUpdating = False
        Set wb = Workbooks.Open(fn)
        thisWb.Activate
        Application.ScreenUpdating = True
        frmSheetSelect.selectSheets wb.Name, wb.Sheets
        a = frmSheetSelect.getSelectedSheets
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        If IsArray(a) Then
            For Each sht In wb.Sheets(a)
                Debug.Print wb.Name & " | " & sht.Name & " - Deleted row: " & sht.Cells.SpecialCells(xlCellTypeLastCell).EntireRow.Address
                sht.Cells.SpecialCells(xlCellTypeLastCell).EntireRow.Delete
            Next sht
            wb.Close True
        Else
            wb.Close False
            If a Then
                thisWb.Activate
                Application.EnableEvents = True
                Application.ScreenUpdating = True
                Set thisWb = Nothing
                Set wb = Nothing
                Exit Sub
            End If
        End If
        thisWb.Activate
        Application.EnableEvents = True
        Application.ScreenUpdating = True
        Set wb = Nothing
    Next fn
    Set thisWb = Nothing
    MsgBox "Done :)", vbInformation
End Sub

it uses a userform though. This link is a file with that code implemented: Delete last row.xlsm

userform code:

Option Explicit

Private selectedSheets

Public Sub selectSheets(ByVal wbName As String, ByVal sheetArray)
    Dim sht
    lblWorkbook.Caption = wbName
    On Error Resume Next
    For Each sht In sheetArray
        If VarType(sht) = 8 Then lstSheets.AddItem sht Else lstSheets.AddItem sht.Name
    Next sht
    On Error GoTo 0
    If Not Me.Visible Then Me.Show
End Sub

Public Function getSelectedSheets() As Variant
    getSelectedSheets = selectedSheets
    Unload Me
End Function

Private Sub btnCancel_Click()
    Me.Hide
    selectedSheets = False
End Sub

Private Sub btnOK_Click()
    Dim i&
    For i = 0 To lstSheets.ListCount - 1
        If lstSheets.Selected(i) Then
            If IsArray(selectedSheets) Then
                ReDim Preserve selectedSheets(1 To UBound(selectedSheets) + 1)
            Else
                ReDim selectedSheets(1 To 1)
            End If
            selectedSheets(UBound(selectedSheets)) = lstSheets.List(i)
        End If
    Next i
    If IsArray(selectedSheets) Then
        Me.Hide
    Else
        MsgBox "No sheets selected", vbCritical
    End If
End Sub

Private Sub lblAll_Click()
    Dim i&, b As Boolean
    b = lblAll.Caption = "Select All"
    For i = 0 To lstSheets.ListCount - 1
        lstSheets.Selected(i) = b
    Next i
    lblAll.Caption = IIf(b, "Select None", "Select All")
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = 0 Then
        Me.Hide
        selectedSheets = True
    End If
End Sub

 

[ggji90]

For your first question try looking at Personal Workbooks;

Make A Personal Macro Workbook

Macros stored in the Personal Macro Workbook are always at your disposal. You can run the macros on any workbook that you open on the computer. Also, you do not have to jump through security hoops to run macros stored in the Personal Macro Workbook.

www.mrexcel.com

Not really sure what you mean by the second question, it'd be a bit dangerous to open every single Workbook you have on your PC and delete the last row.

You can write code that'll open up all Workbooks in a specific folder, execute your Macro and then save it again which might be the better option so you've got some control over which Workbooks get there last row deleted.

You can find a ton of examples of code that opens all workbooks in a folder and runs a macro, this thread for example;

Open all files in a folder, run a macro

I have 2,000 spreadsheets in a folder - I would like some code that; - opens each spreadsheet one at a time - in sheet "Summary", copy/paste special whole sheet - in sheet "Summary", columns C&D - I only want the first letter of any text to remain (converting names to initials) - delete all...

www.mrexcel.com

You'll need to tailor it to your specfic needs though.

I meant was that I design a macro once and can use it anytime I want in any file I open. I didn't mean I will execute it in every file I open but would like to have the option to. I will check the rest of your solution and see what works for me. Thanks for the reply.

 

[trunten]

In that case, just stick this in your personal macro workbook and be done. you could even add a toolbar item if you were that way inclined

Sub deleteLastRow()
    On Error Resume Next
    ActiveSheet.Cells.Find("*", Range("A1"), xlFormulas, 2, 1, 2, False).EntireRow.Delete
    On Error GoTo 0
End Sub