Automatically Deleting Empty Rows

[suzyg]

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!)

 

[HalfAce]

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]

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 .

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]

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 )

 

[suzyg]

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 )

Thanks anyway.

 

[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?

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.

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.