Excess of blank rows

[andrewb90]

I have a macro that I am using and it seems to create an excess of lines at the bottom of the sheet that I not only am unable to delete, but cause me problems when trying to print. (I only want to print pages with information on it not, 150 extra pages with only a cell outline.)

Here is what I am using:

Sub MONDAY()

Application.ScreenUpdating = False

With Sheets("Route")
.Rows("1:3000").Interior.Pattern = xlNone
.Rows("3:3000").Hidden = False
    For Each cell In .Range("A3:A" & .Range("A" & Rows.Count).End(xlUp).Row)
        If cell.Value = "Inactive" Or _
        cell.Value = "Weekend" _
        Or cell.Value = "ST" _
        Or cell.Value = "TV" _
        Or cell.Value = "FD" _
        Or cell.Value = "NFD" _
        Or cell.Value = "" Then
            cell.EntireRow.Hidden = True
        End If
    Next
.Range(.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0), .Cells(Rows.Count, "A")).EntireRow.Delete
End With
Sheets("Route").Select
Rows("3:5").Hidden = False
Rows("6:8").Hidden = True
Rows("9:30").Hidden = True
Range("A1").Select
Application.ScreenUpdating = True

End Sub

I am running Win 7 & Excel 2010

Any ideas on fixing this would be extremely helpful.

- Andrew

 

[Joe4]

It might be with these two rows here:

.Rows("1:3000").Interior.Pattern = xlNone
.Rows("3:3000").Hidden = False

You are formatting them down to row 3000.

Why not just fueld your last row and format down to there? You already have the code for that here:

.Range("A" & Rows.Count).End(xlUp).Row

 

[andrewb90]

I'm not entirely sure how I would write that.

 

[Joe4]

Pretty much everything you need is already written (I am guessing then that maybe that this code isn't something you wrote yourself, so you might not follow all of the methodologies used?).

This is what the updated code would look like:

Sub MONDAY()
 
Application.ScreenUpdating = False
 
Dim myLastRow As Long
 
'Find last row in column A
myLastRow = Range("A" & Rows.Count).End(xlUp).Row
 
With Sheets("Route")
.Rows("1:" & myLastRow).Interior.Pattern = xlNone
.Rows("3:" & myLastRow).Hidden = False
    For Each cell In .Range("A3:A" & myLastRow)
        If cell.Value = "Inactive" Or _
        cell.Value = "Weekend" _
        Or cell.Value = "ST" _
        Or cell.Value = "TV" _
        Or cell.Value = "FD" _
        Or cell.Value = "NFD" _
        Or cell.Value = "" Then
            cell.EntireRow.Hidden = True
        End If
    Next
'I don't think the following row is necessary anymore, so I commented it out
''.Range(.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0), .Cells(Rows.Count, "A")).EntireRow.Delete
End With
 
Sheets("Route").Select
Rows("3:5").Hidden = False
Rows("6:8").Hidden = True
Rows("9:30").Hidden = True
Range("A1").Select
 
Application.ScreenUpdating = True
 
End Sub

 

[andrewb90]

Thanks! I only understand select aspects of the code at this point.
It does work however I still am seeing something odd when I attempt to print. The sheets with data on them only goes up to page 17 yet there are 36 pages that it tries to print. The other pages only have cell outlines on them.
I was trying to remove the excess lines to help with adding new information on the page, but the printing issue seems to still be here.


Any ideas what would cause this?

- Andrew

 

[Joe4]

Maybe someone pre-formatted the sheet before (outside of the macro).

Try uncommenting the delete row that I commented out in the code and re-run the macro. Then save the file. Then try printing it.

Does that make any difference?

 

[andrewb90]

It didn't make any difference. I still have all those pages trying to print.
I have this code that I tend to use before testing my monday code, but I don't think this would be causing a problem:

Sub SHOW_ALL_ROWS()
    Sheets("Route").Select
    Rows("1:2000").Hidden = False
    Range("A1").Select
End Sub

The only other thing that I could think of is some conditional formatting that I have in place. I do have macros for the other days of the week but haven't been regularly using them when I have been trying a revised code. I have just been showing all cells and then hiding select cells with the monday code.

thanks,

Andrew

 

[Joe4]

The only other thing that I could think of is some conditional formatting that I have in place.

That could be an issue. How far down have you applied the Conditional Formatting?

Is that consistent with the number of pages that are printing?