Need help with technique to copy cells but omit blanks

[rrmando]

Hello all. Is there a way to use an IF statement to copy multiple cells onto another sheet? I have data across 4 cells (multiple rows) and need to omit the rows that have a zero value in any of the 4 cells.

I am using columns A thru D and columns B and D will be the ones that sometimes contain a zero value. I need to copy over the rows that contain data across all 4 cells only. I am trying to filter but am not having much luck. Thank you.

 

[pgc01]

Hi rrmando

You can us advance filter to copy the data. Use the criteria formula

=AND(B2<>0,D2<>0)

since only these 2 columns can contain 0.

Hope this helps
PGC

 

[pgc01]

Hi again

I read your post again and the title is not coherent with the text. In the title you say "omit blanks", in the text you say "omit zero values".

The criteria formula I gave is for what you wrote in the text: "omit zero values".

If instead it's as in the title "omit blanks" use

=AND(B2<>"",D2<>"")

Kind regards
PGC

 

[rrmando]

Thanks for the reply PGC. I apologize for my confusing title, I should have written to OMIT ZERO VALUES. How do I enter this criteria formula to filter over multiple rows? For example, from b2:b100 and d2:d100. Thanks again for your help.

 

[pgc01]

Hi rrmando

I prepared a small example with different but maybe easier to understand criteria.

My data are in A1:D10, with the headers in row 1.

The criteria are in F1:G2. For each field that you want to use in the criteria you write the header and below the conditions.
In this case we want the conditions Field 2<>0 and Field 4<>0.

I think this is the case in your real table.

Now to use the Advanced Filter

Data>Filter>Advanced Filter

List range - $A$1:$D$10
Criteria range - $F$1:$G$2

Click on Copy to another location

Copy to - $I$1

Click OK

You can check the result in the image I posted.

Now you can copy the result to another sheet.

Hope this is what you need.
PGC

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Field1	Field2	Field3	Field4		Field2	Field4		Field1	Field2	Field3	Field4
2	14	12	7	17		<>0	<>0		14	12	7	17
3	15	0	9	13					11	16	14	10
4	11	16	14	10					2	20	16	18
5	1	0	17	0					9	15	18	8
6	2	20	16	18					9	5	15	20
7	18	9	10	0
8	9	15	18	8
9	10	0	6	14
10	9	5	15	20
11
12
Sheet1

 

[pgc01]

P. S. Sorry, the tool that generates the image doesn't like the < sign, because of HTML.

In F2 and G2, read

<>0

 

[rrmando]

Thanks PCG. I haven't had a chance to get back on here for a few days, but this is what I ended up using after further research in case anyone else can use:

Sheets("Staff Vacation").Select
    Sheets("Staff Vacation").Copy After:=Sheets(1)
    Sheets("Staff Vacation (2)").Select
    Sheets("Staff Vacation (2)").Name = "JE"
    Selection.RemoveSubtotal
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("A:F").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    
    Dim lRow As Long 'Last Row
Dim cnt As Long
lRow = Range("B" & Rows.Count).End(xlUp).Row
For cnt = lRow To 1 Step -1
    If Range("B" & cnt) = 0 Then Rows(cnt).Delete
Next

Thanks again for all your help.

 

[pgc01]

Hi rrmando

I'm glad you found a solution convenient to you and thanks for posting it.

Cheers
PGC

 

[rrmando]

I can't thank you enough for your explanation pgc. I was not aware that you could do that with Advanced Filter. Even though I did not end up using it in this particular situation, I know I will be able to apply in others. Thanks again.