Delete blank cells and corresponding dates

[excelV]

I have a bunch of columns with data with corresponding dates. The problem is that I have some invalid (#value!) and need to get rid of those. I have tried replacing the invalid cells with blanks but I still can't delete them. I have about 800 of these so I have been trying to create a macro that can do this in one shot.


Date Liquidity Date Liquidity
11/29/2007 0.057586008 7/30/2002 0.097560976
11/30/2007 0.059781879 8/1/2002 0.142857143
12/4/2007 0.057720572 8/2/2002 #VALUE!
12/5/2007 0.05913211 8/6/2002 0.077803204
12/6/2007 0.058447471 8/7/2002 0.098765432

 

[GK039]

Copy Paste to your VB Editor:

Sub delinvalid()
Dim i As Integer
Dim lrow As Integer


Range("a1").Select
lrow = Selection.End(xlDown).Row

For i = lrow To 1 Step -1
If IsError(Cells(i, "A").Value) Then Cells(i, "a").EntireRow.Delete

Next i


End Sub

Change cell A1 with the range that suits you

George

I have a bunch of columns with data with corresponding dates. The problem is that I have some invalid (#value!) and need to get rid of those. I have tried replacing the invalid cells with blanks but I still can't delete them. I have about 800 of these so I have been trying to create a macro that can do this in one shot.


Date Liquidity Date Liquidity
11/29/2007 0.057586008 7/30/2002 0.097560976
11/30/2007 0.059781879 8/1/2002 0.142857143
12/4/2007 0.057720572 8/2/2002 #VALUE!
12/5/2007 0.05913211 8/6/2002 0.077803204
12/6/2007 0.058447471 8/7/2002 0.098765432