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

Mary Bizmax

New Member
Joined
Aug 12, 2018
Messages
4
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.
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,610
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.

Code:
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

New Member
Joined
Aug 12, 2018
Messages
4
Re: VBA Code for deleting empty rows at the end of a sheet please

Thank you so much, works beautifully.
Very happy :)
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,610
Re: VBA Code for deleting empty rows at the end of a sheet please

You are welcome. Glad to help.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,543
Messages
5,529,456
Members
409,878
Latest member
DDhol
Top