Copy value from next cell in a row if cell value is NaN

[sdaetwyler]

Hi

I have an excel sheet with cells mostly filled by numeric values. But
in each row some cells wont have a value but a "not available number"
(NaN) instead.

So what I'm looking for is a macro that checks if a cell contains a
NaN. If that's the case go one (or more) column(s) to the right in this
row until there is the next numeric value and copy/paste this value to
the cell where the NaN has been found in the first place. Do this for
all cells in the row, then jump one row down and start over again.

input:
320 300 NaN 310 NaN 400 380 NaN NaN 370
330 308 NaN 313 NaN 407 379 NaN NaN 378
....

desired output:
320 330 310 400 380 370
330 308 313 407 479 378
.....

thanks for your help
Stefan

 

[Guest]

Try this (but make sure you have backed up you data!):

Sub removeNan()
With Worksheets("Sheet1")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To lastrow
lastcol = .Cells(r, Columns.Count).End(xlToLeft).Column
For col = lastcol To 1 Step -1
If .Cells(r, col) = "NaN" Then .Cells(r, col).Delete
Shift:=xlToLeft
Next col
Next r
End With
End Sub

HTH

 

[sdaetwyler]

Works great.
Thanks alot for your fast reply and the helpful code.

 

[Guest]

Thanks for feedback.