Conditional Printing

[joshlaf]

Good Morning!

I have a bit of a question. Right now, I'm trying to make an inventory of the supply room which I will keep updated.

I'm just wondering if there's a way that excel can ignore if, for example, G7 is empty so therefore it will not print the whole row.

This is a very crude representation of what I'm trying to do, so I want to be able to print all the rows except the blank ones. I know I can sort by blank but I do not want to mess up the order of items. I've tried a few VBA codes, but none seem to do what I'm looking for.

 

[gallen]

Hello and welcome.

Any reason you can't just filter out blanks and print that?

 

[joshlaf]

I mean that is a possiblity, is there a macro in which I'd be able to do that? I'm just trying to minimize the steps.

If I could just run the macro, and it prints that would save me time. I found this VBA code in the search but it doesn't seem to be working.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Range("E").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<>0", Operator:=xlAnd
End Sub

 

[gallen]

Yes that's a little off.
this line will not work and selecting is rarely the best way of doing things:

[COLOR=#333333]Range("E").Select[/COLOR]

Is your table always the same range or is it dynamic. First issue is to ensure we always know what range the full data table covers (unless of course you have a named table)

Then you'd filter out the blanks, print and then show blanks again. Is this correct?

 

[gallen]

This code will hide ALL blank rows print and then unhide them:

Sub HideAndPrint()
    With ActiveSheet
        .UsedRange.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
        .PrintOut
        .UsedRange.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = False
    End With
End Sub

 

[joshlaf]

Thank you! I ended up getting there from rampant googling lol .

Sub FilterAndPrint()
Range("A1:E1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:=">=0"
Range("A1:E1").Select
Range("B:B").EntireColumn.Hidden = False
Range("C:C").EntireColumn.Hidden = True
Range("D:D").EntireColumn.Hidden = True
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter
Range("B:B").EntireColumn.Hidden = True
Range("C:C").EntireColumn.Hidden = False
Range("D:D").EntireColumn.Hidden = False
End Sub

I'm assuming I can make it ignore rows as well?

Thanks so much