Remove empty rows (containing formulas) before printing

[geertech]

I have a spreadsheet for invoicing with several tabs, for my own reference and calaculations and one for printing the actual invoice to customers.

The printing tab has formulas refering to the 'input' tabs, that obviously in a lot of cases has too many rows as there's not so much to invoice.

I would like to remove the empty rows (they do contain the formulas and some formatting) before printing, but can't seem to find a solution, tried several suggestions, and recording macro's but no luck so far...

Would anyone be able to resolve this?

Thanks a million,
Remco

 

[Michael M]

Maybe something like this
Of course, change the column reference

Sub MM1()
ActiveSheet.Columns("A").SpecialCells(xlCellTypeFormulas).EntireRow.Delete
End Sub

 

[geertech]

Maybe something like this
Of course, change the column reference

Sub MM1()
ActiveSheet.Columns("A").SpecialCells(xlCellTypeFormulas).EntireRow.Delete
End Sub

Thanks Michael, but it gives me a runtime error 1004, although I've set the trust center to allow macro's...

 

[Michael M]

Did you use the correct column ref.
Also, the macro willerror if nothing is found.

 

[geertech]

Did you use the correct column ref.
Also, the macro willerror if nothing is found.

No, I didn't, silly...
When pointing to the correct column it works, but deletes the non-empty rows as well though..

 

[Michael M]

Maybe this then

Sub MM2()
Dim lr As Long, r As Long
lr = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
    For r = lr To 2 Step -1
        If Range("D" & r).HasFormula() = True And Range("A" & r).value = "" Then Rows(r).Delete
    Next r
End Sub

Rememeber, change the "D" to the column that has the formulas !!!

 

[jolivanes]

Maybe I am missing something but could you not set the print range to the range excluding the "empties" that have formulae?

 

[arthurz11]

I suggest that you highlight the column(s) or row(s). And if you press and hold the control key, so you can select additional column(s) or row(s) which are not adjacent. Then right-click the selected column(s) or row(s) and select hide. It hides it and it does not interfere with any formulas at all. After you print it, you can unhide it. Hope it helps.

 

[geertech]

Maybe this then

Sub MM2()
Dim lr As Long, r As Long
lr = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
    For r = lr To 2 Step -1
        If Range("D" & r).HasFormula() = True And Range("A" & r).value = "" Then Rows(r).Delete
    Next r
End Sub

Rememeber, change the "D" to the column that has the formulas !!!

Hi Micheal,
Changed If Range("D" & r).HasFormula() = True And Range("A" & r).value = "" Then Rows(r).Delete
value = ""to value = Empty, thenj it runs like a charm!

Thanks so much for your support!
Cheers,
Remco

 

[Michael M]

Glad to help