Automatically Deleting Empty Rows

suzyg

New Member
Joined
Feb 21, 2010
Messages
10
Hi there - I am creating an invoice template where there are multiple rows to be completed depending on the volume of work the contractor has done. On many occasions there are far too many blank rows left once the invoice is finished. Is there a formula/macro available to automatically delete empty rows when saving the invoice?

Thanks (ps I am a complete macro-beginner!)
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Hello suzyg, welcome to the board.
Sure, this can be automated & executed with the BeforeSave event.
What is your sheet name & range of interest to check for the blank rows?
 

TheIAAway

New Member
Joined
Feb 22, 2010
Messages
28
Click "Alt+F11" to enter the VBA editor. double click on "This Workbook" and enter the code below into the editor.

Select the the column where the cells are blank and click "Macros" and run "DeleteBlankRows1"

This will delete and blank rows.

This can be triggered to run on save but I suggest running it manually .

Code:
Sub DeleteBlankRows1()


Dim i As Long




    With Application

        .Calculation = xlCalculationManual

        .ScreenUpdating = False

      

   

    For i = Selection.Rows.Count To 1 Step -1

        If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then

            Selection.Rows(i).EntireRow.Delete

        End If

    Next i



        .Calculation = xlCalculationAutomatic

        .ScreenUpdating = True

    End With

End Sub
Let me know if this helps.



Jeffrey Scott
The IAA Way
 

suzyg

New Member
Joined
Feb 21, 2010
Messages
10
Thanks HalfAce - the sheet name is simply 'invoice' and the range of interest is columns A-D rows 17-68 and then 75-100....is this what you mean?

Thanking you again :eek:)
 

suzyg

New Member
Joined
Feb 21, 2010
Messages
10
Thanks very much TheIAAWay, but it needs to be an automated process on saving/closing as contractors using the invoice won't know how to run a macro :eek:)

Thanks anyway.
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
the sheet name is simply 'invoice' and the range of interest is columns A-D rows 17-68 and then 75-100....is this what you mean?
Yes, exactly.

it needs to be an automated process on saving/closing as contractors using the invoice won't know how to run a macro
This should do what you asked every time the workbook gets saved.

Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim DataRng As Range, Rng As Range, DltRng As Range
With Sheets("invoice")
  Set DataRng = Union(.Range("A17:A68"), .Range("A75:A100"))
End With
With Application
  .Calculation = xlCalculationManual
  .ScreenUpdating = False
  For Each Rng In DataRng
    If WorksheetFunction.CountA(Rng.Resize(, 4)) = 0 Then
     If DltRng Is Nothing Then
        Set DltRng = Rng
      Else
        Set DltRng = Union(Rng, DltRng)
      End If
    End If
  Next Rng
  If Not DltRng Is Nothing Then DltRng.EntireRow.Delete
  .Calculation = xlCalculationAutomatic
  .ScreenUpdating = True
End With
End Sub

(ps I am a complete macro-beginner!)
In light of that last statement, to install the code you can do exactly as TheIAAway says; "Click "Alt+F11" to enter the VBA editor. (In the VBAProject pane on the left side) double click on "This Workbook" and enter the code below into the editor."

Hope it helps. Post back if you run into problems.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,295
Messages
5,600,789
Members
414,405
Latest member
Zaurb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top