VBA Code for deleting empty rows at the end of a sheet

[Mary Bizmax]

I'm very new to macros, so please excuse my ignorance.

My workbook contains the following sheets:
"XeroImport" - a report from Xero accounting software exported to excel is pasted in there by the user (so the size varies each month)
"Pivot" which has a pivot table sourced from "XeroImport" which automatically refreshes (I've managed to code that in a macro) and then I've filtered the table to exclude blanks and the same macro copies the data into a new sheet called:
"InputSheet". My problem is that I have specified the table range to copy to be A1:H100 (because the pivot table size will vary each month) and so I end up with a lot of empty rows at the end of the "InputSheet". (eg from row 10 to row 100 this month) So when I try and use this sheet as an import to another cloud system, I'm getting a lot of error messages....so I really need to delete these rows.
Please could someone help either with VBA code to delete these empty rows at the end of the sheet? Probably a very simple question but I've been doing a lot of searching on various forums and nothing I've tried seems to work.
Many thanks.

 

[Logit]

Re: VBA Code for deleting empty rows at the end of a sheet please

.
The following macro will delete all empty rows in the range A1:G25. The range can be change as needed.

After deleting the empty row, it moves the remainder rows below up.

The sheet name can be edited as well.

Option Explicit


Sub DeleteBlankRows()


Dim i As Integer
Dim Rng As Range
Dim WorkRng As Range
Dim xRows As Long
'On Error Resume Next


Set WorkRng = Sheets("Sheet2").Range("A1:G25")


xRows = WorkRng.Rows.Count
Application.ScreenUpdating = False


For i = xRows To 1 Step -1
    If Application.WorksheetFunction.CountA(WorkRng.Rows(i)) = 0 Then
        WorkRng.Rows(i).EntireRow.Delete XlDeleteShiftDirection.xlShiftUp
    End If
Next


Application.ScreenUpdating = True
End Sub

 

[Mary Bizmax]

Re: VBA Code for deleting empty rows at the end of a sheet please

Thank you so much, works beautifully.
Very happy

 

[Logit]

Re: VBA Code for deleting empty rows at the end of a sheet please

You are welcome. Glad to help.