I wonder if it is possible through a macro to be able to delete an entire row when the result of a searchv is #N/A, I tried to do it through the option to record macro with a filter and delete those that do not match, but when running the This macro runs the search formula for me and it only leaves me the first rows.
I tried this code, but it's giving me error 13 type mismatch
Dim Borrar As Range
Dim i As Long
With Application
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False
End With
With ThisWorkbook.Sheets("Exportar")
For i = 2 To .UsedRange.Rows.Count
If .Cells(i, "D") = "#N/D" Then
If Borrar Is Nothing Then
Set Borrar = .Cells(i, 1)
Else
Set Borrar = Union(Borrar, .Cells(i, 1))
End If
End If
Next i
End With
Borrar.EntireRow.Delete
With Application
.Calculation = xlCalculationAutomatic
.EnableEvents = True
.ScreenUpdating = True
End With
.Cells(i, "D")
returns a value of type range..Cells(i, "D").value
return an "Error" data type, because the formula returns an error.In both cases it does not match "#N/D" which is of type text.
the solution is to use .Text, it looks like this:
In the for cycle there is also a detail, if for example there are 10 rows to process, but since you are starting the cycle at 2, it means that the cycle will be repeated 9 times, I understand that you start it at 2 because it is the first row to process is number 2, but one thing is in which row it starts and another is the number of times the cycle is repeated.
This can be fixed in 2 ways.
1.- in each
.Cells(i
one is added 1 ai, being like this:.Cells(i +1
2.- You add 1 to the row count itself:
.UsedRange.Rows.Count + 1
This is how the code looks.