# Need help with technique to copy cells but omit blanks

#### rrmando

##### Board Regular
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.

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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

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

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.

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

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

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

In F2 and G2, read

Code:
``<>0``

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.

Hi rrmando

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

Cheers
PGC

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.

Replies
3
Views
196
Replies
1
Views
130
Replies
2
Views
178
Replies
0
Views
380
Replies
3
Views
336

1,218,946
Messages
6,145,383
Members
450,613
Latest member
Chalky1982

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back