Conditional delete based on cell in same row

Microsoft Microsoft excel 2004 (mac)
August 3, 2009 at 23:23:16
Specs: Macintosh
Hi there,

I have 2 different parameters (temperature and activity) that I have recorded from 12 different subjects, with each parameter's results displayed in adjacent (12) columns. On occasion, there was no data obtained for one parameter in one subject, therefore this parameter's cell reads NaN - I would like to then delete the cell that carries the other parameter, as the reading was likely not a good one. The problem is, the corresponding cell (the other parameter from the same subject, is 12 columns away (same row). Is there a way to code: if cell reads NaN, delete cell in the same row, 12 columns to the left?

Thanks very much for any pointers!!


See More: Conditional delete based on cell in same row

Report •


#1
August 3, 2009 at 23:33:29
it would be ideal if I could even clear one cell in addition to those that are missing data in the other parameter. For example, if in column 13, row 5-10 were missing data (or NaN in this case) then it would be ideal to clear row 5-11 in column 1 (the column corresponding to the same subject, but other parameter). This is because, after a gap in recording, the first proper recording is often affected by the gap rather than the body's physiology itself.


Thanks! There are many many minutes of data, and I am not sure if I could do it all manually


Report •

#2
August 4, 2009 at 03:58:46
Something like this?

Sub Del_NaN()
'Find last Row in Column 13
 lastRow = Range("M" & Rows.Count).End(xlUp).Row
  For Each P In Range("M1:M" & lastRow)
'Delete Data In A if M = NaN
   If P = "NaN" Then Cells(P.Row, "A").ClearContents
  Next
End Sub


Report •

#3
August 4, 2009 at 10:26:15
Thanks very much - that works well. Is there also a way that I could add in an additional row to be deleted in column A - something like
If P = "NaN" Then Cells(P.Row, "A").ClearContents
If P = "NaN" Then Cells(P+1.Row, "A").ClearContents

That way one more cell in column A could be deleted (this is where funny numbers build up from the time when the reading was not available)

Thanks again
Jess


Report •

Related Solutions

#4
August 4, 2009 at 10:54:25
Close.

Instead of Cells(P+1.Row, "A") it's Cells(P.Row + 1, "A")

P.Row evaluates to a number upon which you can perform any arithmetic operation.

However, keep in mind that if you add that line, it's going to clear every Cells(P.Row + 1, "A") even if there isn't an NaN in Cells(P.Row + 1, "M").

In other words, will you always have extraneous data in Cells(P.Row + 1, "A") that you will want to clear?

P.S. If you plan to add that line, it's more efficient to check P once and perform whatever actions you want to perform based on that single test. In other words, code it like this:

Sub Del_NaN()
'Find last Row in Column 13
 lastRow = Range("M" & Rows.Count).End(xlUp).Row
  For Each P In Range("M1:M" & lastRow)
'Delete Data In A if M = NaN
   If P = "NaN" Then 
    Cells(P.Row, "A").ClearContents
    Cells(P.Row + 1, "A").ClearContents
   End If
  Next
End Sub


Report •

#5
August 4, 2009 at 11:13:15
Ah, I see.

Thank you for your help!


Report •


Ask Question