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

[ourmick]

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?

 

[MattH1]

Let me know if this works.

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

 

[ourmick]

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]

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.