VBA: Delete last 7 rows in a workbook

[RRS]

Hello,

Is it possible to delete the last 7 rows of a closed workbook. The workbook and worksheet name would always be the same. The number of rows will change. The workbook is a data file that is downloaded and replaced weekly. Therefore, the row numbers would always change, BUT the last 7 rows would always display the grand totals of the worksheet.

I would like to know if it is possible to create a VBA to delete the last 7 rows while keeping the data workbook closed. Therefore when we download the data file we don't have to open it every time to modify, we would just overwrite the last file and run the code from my master workbook.

Thank you,
your help is greatly appreciated.
Please see attachment

 

[Joe4]

If you are going to do it via VBA, what difference does it make if the VBA code momentarily opens the file, deletes the rows, and then saves and closes the file?
It doesn't add any extra burden for anyone - VBA is taking care of it all, and no one is the wiser.

 

[RRS]

If you are going to do it via VBA, what difference does it make if the VBA code momentarily opens the file, deletes the rows, and then saves and closes the file?
It doesn't add any extra burden for anyone - VBA is taking care of it all, and no one is the wiser.

That would be perfect. I need assistance in creating the code.


I was able to find this code but don't understand enough to modify it. I really need help on how to figure out how to delete the last 7 rows.


Sub Macro1()
ScreenUpdating = False
Workbooks.Open Filename:="C:\Users\Parents\Desktop\ThisBook.xlsm"

Windows("Book2.xlsm").Activate
transfer = ActiveSheet.Range("A1").Value

Windows("ThisBook.xlsm").Activate
ActiveSheet.Range("A1").Value = ActiveSheet.Range("A1").Value + transfer

ActiveWorkbook.Save
ActiveWindow.Close
ScreenUpdating = True
End Sub

 

[RRS]

.

 

[Joe4]

Here is a little procedure to demonstrate how you can do that.
If you like, you could even make the file name a variable, so you can make this a general reusable procedure that you just pass in the file name, and it will delete the last 7 rows of that file:

Sub DeleteLast7Rows()

    Dim fname As String
    Dim lr As Long
        
'   Set file name to edit
    fname = "C:\Documents\Book3.xlsx"

'   Open file
    Workbooks.Open Filename:=fname

'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Delete last 7 rows of data
    Rows(lr - 6 & ":" & lr).Delete

'   Save and close workbook
    ActiveWorkbook.Save
    ActiveWindow.Close
    
End Sub

 

[RRS]

Here is a little procedure to demonstrate how you can do that.
If you like, you could even make the file name a variable, so you can make this a general reusable procedure that you just pass in the file name, and it will delete the last 7 rows of that file:

Sub DeleteLast7Rows()

    Dim fname As String
    Dim lr As Long
       
'   Set file name to edit
    fname = "C:\Documents\Book3.xlsx"

'   Open file
    Workbooks.Open Filename:=fname

'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Delete last 7 rows of data
    Rows(lr - 6 & ":" & lr).Delete

'   Save and close workbook
    ActiveWorkbook.Save
    ActiveWindow.Close
   
End Sub

Remarkable. Thank you for all your help. I am amazed how powerful excel is.

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[RRS]

You are welcome.
Glad I was able to help!

Hello friend.

I need one last help with this. After running this macro, of course I would need to refresh my query and all my pivot tables attached.

I created a VBA to refresh all my tables and pivot tables but I have to run the function twice. How can I correct this code or how can I have the refresh macro run twice.


Sub Refresh_Data()

'Refresh data source
ActiveWorkbook.RefreshAll

'Refresh Pivot Tables
Dim PT As PivotTable
Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets

For Each PT In WS.PivotTables
PT.RefreshTable
Next PT

Next WS

'Refresh Worksheet in "EmpLoan PTable" - Worksheet is hidden
Worksheets("EmpLoan PTable").PivotTables("PivotTable1").RefreshTable


'Message Box
MsgBox "All Data Tables Refreshed"


End Sub

 

[Joe4]

Hello friend.

I need one last help with this. After running this macro, of course I would need to refresh my query and all my pivot tables attached.

I created a VBA to refresh all my tables and pivot tables but I have to run the function twice. How can I correct this code or how can I have the refresh macro run twice.


Sub Refresh_Data()

'Refresh data source
ActiveWorkbook.RefreshAll

'Refresh Pivot Tables
Dim PT As PivotTable
Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets

For Each PT In WS.PivotTables
PT.RefreshTable
Next PT

Next WS

'Refresh Worksheet in "EmpLoan PTable" - Worksheet is hidden
Worksheets("EmpLoan PTable").PivotTables("PivotTable1").RefreshTable


'Message Box
MsgBox "All Data Tables Refreshed"


End Sub

That is an entirely different question, and thus should be posted to a new thread.
(I don't know the answer anyhow!)

 

[RRS]

That is an entirely different question, and thus should be posted to a new thread.
(I don't know the answer anyhow!)

Thank you, for all your help. I appreciated the assistance