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?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

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.
 

Forum statistics

Threads
1,172,030
Messages
5,878,812
Members
433,372
Latest member
QuestioningControllerBE

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