Code to print columns with active rows not working

**debbiesh** · 07-09-2009, 01:33 PM

Here's the code I have for each column. This is for column H, Routing --

Sub Print_Routing()
'

Dim PrintArr As Variant
PrintArr = Array("H")
For i = 0 To UBound(PrintArr)
Rows("1:2").Select
    Selection.EntireRow.Hidden = True
    
    Columns("H:H").Select
    Selection.AutoFilter Field:=8, Criteria1:="<>"
    Columns("D:EV").Hidden = True
    Columns(PrintArr(i)).Hidden = False
    ActiveSheet.PrintPreview
Next i
     Columns("D:EV").Hidden = False
    Selection.EntireRow.Hidden = False
        Selection.AutoFilter Field:=10
     
End Sub

**debbiesh** · 07-10-2009, 09:08 AM

I figured out what was wrong with my code. So this works to where I can now click the button and the correct columns will do their thing -- autofilter to show non-blank cells, show print preview, then show all again.

Sub Print_Routing()
'

Dim PrintArr As Variant
PrintArr = Array("H")
For I = 0 To UBound(PrintArr)
Rows("1:2").Select
    Selection.EntireRow.Hidden = True
    
    Columns("H:H").Select
    Selection.AutoFilter Field:=8, Criteria1:="<>"
    Columns("D:EV").Hidden = True
    Columns(PrintArr(I)).Hidden = False
    ActiveSheet.PrintPreview
Next I
Rows("1:2").Select
     Columns("D:EV").Hidden = False
    Selection.EntireRow.Hidden = False
         ActiveSheet.ShowAllData

     
End Sub

The only thing I can't do now is print down to the last active cell. I must've lost that code while messing with the rest of it.

**shg** · 07-10-2009, 10:00 AM

Reset the print area to the whole sheet, Debbie.

**debbiesh** · 07-10-2009, 10:26 AM

Thanks for replying shg!

I don't know what happened but the code above now wants to include Columns A - C in the print preview, rather than B - C.

Also, it wants to print rows 4, 5, 7, and 10 no matter what active cells are in the column to be printed. *sigh*

Would it have to do with resetting the print for the whole sheet?

**shg** · 07-10-2009, 10:28 AM

Rewind a bit and explain what you want.

**debbiesh** · 07-10-2009, 11:07 AM

You're right, it does sound a bit confusing. Lemme try again.

I created six PRINT buttons, one for each column -- H, I, J, K, L, and M. I want the user to be able to click the button over one of those columns to print that particular column WITH columns B and C. I also want to only print the non-blank cells within that column.

So I'm trying to create a code that will autofilter a column to include only non-blank cells, and then print columns B, C, and that chosen column, down to the last active cell.

Make sense? Attached is another sample. I appreciate your help with this. I'm trying to make our production manager's job a little easier. :-)

**shg** · 07-11-2009, 05:14 PM

Debbie, try this:

Option Explicit

Sub PrintRouting()
    PrintCol "H3", Array("A", "D:EV")
End Sub

Sub Print_Metal()
    PrintCol "I3", Array("A", "D:EV")
End Sub

Sub Print_Trim()
    PrintCol "J3", Array("A", "D:EV")
End Sub

Sub Print_Vinyl()
    PrintCol "K3", Array("A", "D:EV")
End Sub

Sub Print_Print()
    PrintCol "L3", Array("A", "D:EV")
End Sub

Sub Print_Paint()
    PrintCol "M3", Array("A", "D:EV")
End Sub

Sub PrintCol(sRng As String, avHide As Variant)
    Dim i As Long
    Dim v As Variant
    
    With ActiveSheet
        .AutoFilterMode = False
        .Rows("1:2").Hidden = True

        .PageSetup.PrintArea = .UsedRange.Address
    
        For Each v In avHide
            .Columns(v).Hidden = True
        Next v
        
        With .Range(sRng).EntireColumn
            .Hidden = False
            .Range(sRng).AutoFilter Field:=.Column, Criteria1:="<>"
        End With
        .PrintPreview
        
        .AutoFilterMode = False
        .Columns.Hidden = False
        .Rows.Hidden = False
    End With
End Sub