PDA

View Full Version : Printing issue



doubtfire
10-12-2012, 04:51 PM
I have the following data from a worksheet for printing.
Col1 Col2 Col3
a 1 2
b
c 2 21
d
e 11 22

Can I get rid of the "b" and "d" since no data from Col2 and Col3 when printing, and just print like this
Col1 Col2 Col3
a 1 2
c 2 21
e 11 22

Thanks.:think:

jolivanes
10-12-2012, 08:12 PM
See if this works.
Change PrintPreview to Printout to print instead of viewing the page.

Sub TryThis()
Application.ScreenUpdating = False
Range("A1:A" & Cells(Rows.Count, 1).End(xlUp)).AutoFilter 1, ">0"
ActiveSheet.PrintPreview 'PrintOut
Range("A1:A" & Cells(Rows.Count, 1).End(xlUp)).AutoFilter
Application.ScreenUpdating = True
End Sub

doubtfire
10-13-2012, 07:39 AM
Thanks.:whistle:

doubtfire
10-14-2012, 06:07 PM
One more request.
If I have
Col1 Col2 Col3
a 1 2
b 0 11
c 2 21
d 0 0
e 11 22
f 23 0

The above "b" has Col3 > 0 AND "f" has Col2 >0
and the final result is ONLY "d" is eliminated,
what will the new autofilter criteria (mulitple) be ?
Thanks.:friends:

jolivanes
10-14-2012, 10:05 PM
Just so we don't misunderstand each other.
Do you want only the rows printed where all three columns (A, B and C) have some value in the rows? In other words, if one of the cells is blank it should not be printed.
So if A3 and B3 have a value but C3 has not, you don't want that row printed?
BTW, your columns are 1, 2 and 3 and the rows are A, B, C etc. On my computer it is the other way around. The Columns are alphabetical and the Rows are numerical.

Regards
John

doubtfire
10-15-2012, 09:31 AM
Sorry for not being clear.
When Col2 and Col3 values both are 0 (zero) then NOT to print.
Therefore on the following data
Col1 Col2 Col3
a 1 2
b 0 11
c 2 21
d 0 0
e 11 22
f 23 0
"b" and "f" has either Col2 and Col3 zero and they do not fulfill my requirements.
ONLY "d" has Col2 and Col3 have both zeros.
Awaiting for your advice code.
Thanks.: pray2:

jolivanes
10-15-2012, 06:22 PM
See if this works for you.
It hides the rows that are blank in columns A, B and C or are blank in columns B and C.
Note: There is a difference in having a zero (0) and a blank cell.
It also should have nothing in the columns to the right. It still works if you do but it finds the last used cell with the "UsedRange" statement.


Sub TryThis_A()
Dim c As Range
Dim lc As Long
Dim i As Long
Application.ScreenUpdating = False
lc = Range("A" & ActiveSheet.UsedRange.Rows.Count).Row
For i = lc To 1 Step -1
If Cells(i, 1).Offset(, 1).Value = "" And Cells(i, 1).Offset(, 2).Value = "" Then Cells(i, 1).EntireRow.Hidden = True
Next i
Application.ScreenUpdating = True
ActiveSheet.PrintPreview 'PrintOut
Rows("1:" & lc).EntireRow.Hidden = False
End Sub

doubtfire
10-15-2012, 06:35 PM
Thank you again.
One more question (hopefully it is last one or you like me to have a fresh post)
I have the following

Dim r1 As Range
Dim v1 As Variant

Col1 Col2 Col3
A Good Good
B Bad Bad
C Bad Good
D Good Bad
E Good Good

Normally I would
Set r1 = Range("A1:C6")
v1 = r1

My question is how could I insert a filtered range into the variant?
In my case I would like when Col2 ="Bad" and Col3="Bad", yes that's "B"
to be excluded from copying into the variant.
Is it possible !?
Thanks.:bow:

jolivanes
10-15-2012, 09:39 PM
I don't really follow what you mean.
Maybe do start a new thread so someone who is a better at that can answer your question.
Sorry about that.

Regards
John

doubtfire
10-16-2012, 07:03 AM
Thanks.:beerchug: