Need help with technique to copy cells but omit blanks

rrmando

Board Regular
Joined
Dec 23, 2004
Messages
212
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.
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,871
Hi rrmando

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

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

since only these 2 columns can contain 0.

Hope this helps
PGC
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,871
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

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

Kind regards
PGC
 

rrmando

Board Regular
Joined
Dec 23, 2004
Messages
212
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,871

ADVERTISEMENT

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
ABCDEFGHIJKLM
1Field1Field2Field3Field4Field2Field4Field1Field2Field3Field4
21412717<>0<>01412717
315091311161410
4111614102201618
510170915188
62201618951520
7189100
8915188
9100614
10951520
11
12
Sheet1
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,871
P. S. Sorry, the tool that generates the image doesn't like the < sign, because of HTML.

In F2 and G2, read

Code:
<>0
 

rrmando

Board Regular
Joined
Dec 23, 2004
Messages
212

ADVERTISEMENT

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:

Code:
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,871
Hi rrmando

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

Cheers
PGC
 

rrmando

Board Regular
Joined
Dec 23, 2004
Messages
212
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,407
Messages
5,547,771
Members
410,811
Latest member
adustin42
Top