Delete Rows in Another Workbook

Question

Objective: Write VBA script in one Excel workbook (Default.xls) to open another Excel workbook with a variable name (NFL_*). Then the VBA script in Default will delete several superfluous and merged-cell-type rows (1:12) in NFL. Then it will count the number of populated records in NFL (i.e., number of rows) and delete the last row (because it is not really a record, it is just a cell that sums up one of the columns).

Problem: I can't get the latter part to work - the part about deleting the last "row." I can get it to work if the script is written in the NFL workbook itself, but I can't get it to work with the script written in the Default workbook. Though - I can get the VBA script in the Default workbook to delete rows 1:12 in the NFL workbook. Why won't it work for the latter part?

Problem Restated: The Default workbook has 1 VBA script that performs 2 different actions on another workbook. 1 action - deleting rows 1:12 - works. The second action - deleting the last "row" - does not work.

Thank you in advance for any help!

Sub Chop_FR_NFL()

'Set parameters
Dim wb As Excel.Workbook
Dim xlApp As Excel.Application

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

'Open latest NFL report and delete rows 1:12

Set wb = xlApp.Workbooks.Open("T:\xx\xx\xx\xx\xx\xx\xx\" & "NFL_*", True, False)

wb.Sheets(1).Rows("1:12").Delete

'Delete Total UPB Grand Total cell

Dim UPB As Long
UPB = Range("I" & Rows.Count).End(xlUp).Row
Range("I" & UPB).Select
Selection.Delete

wb.Save
wb.Close

End Sub

Good evening Dave, I tried that edit, but the script did not remove the sum from the bottom. Thank you though! — Chase

Tim Williams Tim Williams · Accepted Answer · 2015-05-01T21:39:44

This should work:

'...
With wb.Sheets(1)
    .Rows("1:12").Delete
    .Cells(.Rows.Count, "I").End(xlUp).EntireRow.Delete
End With 
'...

Delete Rows in Another Workbook

1 Answers