Ignore cell if "NaN" Copy if <>"NaN"

ourmick

New Member
Joined
Mar 17, 2009
Messages
35
I have a sheet of data which contains some cells with "NaN" values.
I would like to start at cell A1, check the value, if value = "NaN" move to next cell in the row, continuing along the row
When it finds a cell<>"NaN" I want to copy that cell and paste the value into the same cell on a different sheet.
When it gets to the end of the row I would like to do the same on row 2 starting at A2
Continuing until all rows have been checked

Please can someone point me in the right direction?
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

MattH1

Board Regular
Joined
Jul 15, 2016
Messages
174
Let me know if this works.

Code:
Sub Filter()

'Filters out all NaN
        ActiveSheet.Range("$A:$A").AutoFilter Field:=1, Criteria1:= _
        "<>NaN"

'Pastes all values that aren't filtered into the next column.
Range("B1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

'Unfilter your data
Worksheets("Sheet1").ShowAllData 'You can change "Sheet1" to whatever the name of your worksheet is

End Sub
 
Last edited:

ourmick

New Member
Joined
Mar 17, 2009
Messages
35
Thanks MattH1, but that's not what I need, I wasn't clear enough in my explanation.
There are currently 48 columns and 300+ rows of data.
I need to start at A1, check the value, if the value = "NaN" move to cell B1 and check that value etc. etc. until I get to a cell <>"NaN" (still on row A), copy that cell and paste it into the same cell on a different worksheet. eg cell M1 <> "NaN" so copy cell M1 into sheet2 M1.
Go back to sheet1 row A and continue along doing the same check to the end of the row
Then do the same for each subsequent row.

Thanks
 

MattH1

Board Regular
Joined
Jul 15, 2016
Messages
174
Okay I misunderstood the prompt from the beginning. Let me see if I can think of something and get back to you. It should be a simple double for statement checking each cell independently, but you also have to find the Column and Row length. I'll try and work on it when I have a few seconds of free time and see what I can come up with.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,489
Messages
5,601,986
Members
414,489
Latest member
Xlambda

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?

Disable AdBlock

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
Top