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

[okbishop]

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.

 

[FDibbins]

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]

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]

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

 

[FDibbins]

ADVERTISEMENT

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

 

[okbishop]

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]

ADVERTISEMENT

If you want VBA code, just do an Advanced Filter, with the Criteria of the Amt field being >0
See this for details: http://www.contextures.com/xladvfilter01.html

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]

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]

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]

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