Excel For Loop VBA Macro not working

Question

I have an excel spread sheet with 2 columns, with the last row being 287983. In column A there's numbers, if a cell doesn't have a number NaN is placed there. In, column B there's dates. I'm trying to use the following macro to remove all rows where cells in column A have NaN:

Sub Rowdel()
    Dim i As Long
    For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
        If Cells(i, 1) = "NaN" Then Cells(i, 1).EntireRow.Delete
    Next i
End Sub

However, when I run the above code, the NaN cells are still there. Can anyone suggest why the code isn't working?

Your code worked for me. Do you need to qualify the Cells maybe with a Worksheet. For example Worksheets("Sheet1").Cells(Rows.Count, 1)? — deusxmach1na
This seems to work fine for me. One potential issue may be that your 'NaN' cell contains something other than 'NaN' (different capitalization, different underlying datatype, etc.). Try adding a Debug.Print Cells(i, 1) inside of you loop and verifying that what you get is indeed 'NaN'. — RocketDonkey
another suggestion is that the right Sheet is not active while running this code. This code will only look at the active sheet as it is now. So add a correct sheet reference sheetx.Cells(i,... — K_B
Assuming the problem isn't the sheet reference as already suggested, try Trim(Lcase(Cells(i,1))) = "nan". Or use StrComp(). Sometimes string comparisons can be flakey. — Dave Lewis

JustinJDavies JustinJDavies · Accepted Answer · 2012-11-14T00:26:58

Make sure that this code is referencing the correct Worksheet. You are probably running the code on the wrong worksheet, which contains no NaN in column A and thus leads to no deletions or other visible signs of the code running.

You could try to debug this code using the following (debug-only) version:

Sub Rowdel()
    Dim i As Long
    For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
        Cells(i, 1).Select
        If Cells(i, 1) = "NaN" Then Cells(i, 1).EntireRow.Delete
    Next i
End Sub

From the Excel VBA window, execute the sub using F8, which will allow you to step through the code and check which worksheet you are referencing.

Excel For Loop VBA Macro not working

1 Answers