Printing Issue with deleted content

Question

So we created some code that will allow a user to enter multiple pages of data and then just click a print button to print the number of pages of data they have entered. However we are running into a problem where by if a user enters 300 rows of data, then deletes the last 100 lines, it is still printing 300 rows of data, rather than 200.

Any suggestions?

Code:

Sub LastRowInOneColumn()            
    If Range("C19") = "Enter any special posting instruction here." Then
        Range("C19:I26").ClearContents
    End If

    Dim MySheet As Worksheet
    Set MySheet = Worksheets("SIF Sheet")

    With MySheet
        Dim xFirstEmptyRow As Long
        xFirstEmptyRow = .Cells(30, 1).End(xlDown).Row
        'Application.Dialogs(xlDialogPrinterSetup).Show
        Worksheets("SIF Sheet").PrintOut
    End With    
End Sub

Imagine instead of 30-32, rows of 30-300 filled out with sample data. Then rows 300 back to 200 deleted on purpose(say it wasnt needed). Its still printing up to row 300, instead of to row 200.

Update
I tried playing around with some other variations and came up with this but its throwing an error saying cannot use that command on overlapping selections:

Sub LastRowInOneColumn()            
    If Range("C19") = "Enter any special posting instruction here." Then
        Range("C19:I26").ClearContents
    End If

    Dim MySheet As Worksheet
    Set MySheet = Worksheets("SIF Sheet")

    Rows("30:1058").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.EntireRow.Delete

    With MySheet    
        Dim xFirstEmptyRow As Long
        xFirstEmptyRow = .Cells(30, 1).End(xlDown).Row
        'Application.Dialogs(xlDialogPrinterSetup).Show
        Worksheets("SIF Sheet").PrintOut
    End With    
End Sub

There is no rest of the code. The code was designed to read down the sheet one line at a time, stopping at the point there is no more data and then print at that point. Sorry pasted in wrong code section. — Gehn47, Jan 31 '19 at 21:07
Please post some sample data. the code is just printing the whole page. — Ricardo Diaz, Jan 31 '19 at 21:27
So i would have to manually highlight the rows, right click delete rows in order to not have them print? — Gehn47, Jan 31 '19 at 21:40
could I add something like: `On Error Resume Next Columns("A,B,C,D,E").SpecialCells(xlCellTypeBlanks).EntireRow.Delete` — Gehn47, Jan 31 '19 at 22:53
@Gehn47 never use `On Error Resum Next` without a complete error handling! This line will hide **all** error messages but not only the next one. You might benefit from reading [VBA Error Handling – A Complete Guide](https://excelmacromastery.com/vba-error-handling) • You might also benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Pᴇʜ, Feb 01 '19 at 07:11

score 1 · Accepted Answer · answered Feb 01 '19 at 12:57

Like others are saying, clearing does not delete the records, but even if you delete the rows, excell sometimes keeps the Printing area in memory.

So I would suggest to change the Printing area by macro you can look at https://learn.microsoft.com/en-us/office/vba/api/excel.pagesetup.printarea

Printing Issue with deleted content

1 Answers1