# Check if cell is number and if so copy to different location.

#### okbishop

##### New Member
I have 2 columns A1,B1 A1 is a name B1 is a number . But not every B column will have a number only if A column bought something that month.

Example A and B columns
Frodo 10
Gandalf 5
Sam
Merry 5

I need help with a macro that will copy only the row with a number in B column to a seperate sheet or place on the spread sheet.
any help would be great.

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### FDibbins

##### Well-known Member
Hi, welcome to the forum

Have you considered (or know how to) using filters for this? You can select to show only B with entries or only B without entries.

If you really want to put the list elsewhere...
 A​ B​ C​ D​ E​ 1​ Name Amt Name Amt 2​ Frodo 10​ Frodo 10​ 3​ Gandalf 5​ Gandalf 5​ 4​ Sam Merry 5​ 5​ Merry 5​
D2=IFERROR(INDEX(A:A,SMALL(IF(\$B\$2:\$B\$5<>"",ROW(\$A\$2:\$A\$5)),ROWS(\$A\$1:A1))),"")
ARRAY entered, using CTRL SHIFT ENTER, not just enter
Then copy down and across as needed

#### okbishop

##### New Member
Hi, welcome to the forum

Have you considered (or know how to) using filters for this? You can select to show only B with entries or only B without entries.

If you really want to put the list elsewhere...
 A​ B​ C​ D​ E​ 1​ Name Amt Name Amt 2​ Frodo 10​ Frodo 10​ 3​ Gandalf 5​ Gandalf 5​ 4​ Sam Merry 5​ 5​ Merry 5​

<tbody>
</tbody>

D2=IFERROR(INDEX(A:A,SMALL(IF(\$B\$2:\$B\$5<>"",ROW(\$A\$2:\$A\$5)),ROWS(\$A\$1:A1))),"")
ARRAY entered, using CTRL SHIFT ENTER, not just enter
Then copy down and across as needed

How would I put that into a macro? Or is this a macro all ready?

Thanks

#### okbishop

##### New Member
And Nope I don't know what an Array is?

#### FDibbins

##### Well-known Member
No, that is a formula (probably work quicker than a macro (and no, I cannot put that into a macro for you, sorry)

#### okbishop

##### New Member
No, that is a formula (probably work quicker than a macro (and no, I cannot put that into a macro for you, sorry)

Thanks.. it will not let me copy and past it to the other cells only the first d2 cell. What am I doing wrong?

Thanks

#### Joe4

If you want VBA code, just do an Advanced Filter, with the Criteria of the Amt field being >0

If you turn on the Macro Recorder while you perform these actions, you will get most of the VBA code you need to do this (you might just need to do a little clean-up to make your range selection a little more dynamic).

#### FDibbins

##### Well-known Member
Thanks.. it will not let me copy and past it to the other cells only the first d2 cell. What am I doing wrong?

Thanks

Copy the 1st cell, move to the next cell and start copying from there

#### okbishop

##### New Member
Copy the 1st cell, move to the next cell and start copying from there

My real data also has gaps in it and a lot of rows. Do I need to do this copy/past for each row?

#### FDibbins

##### Well-known Member
My real data also has gaps in it and a lot of rows. Do I need to do this copy/past for each row?

No, just copy down until you start getting blank cells where the formula is

Replies
7
Views
181
Replies
0
Views
184
Replies
2
Views
133
Replies
1
Views
536
Replies
1
Views
124

1,196,027
Messages
6,012,953
Members
441,740
Latest member
abaz21

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

### Which adblocker are you using?

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

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