VBA to Delete Range Based on #N/A not working

[mjayh]

Hello,

I have an Excel 2010 worksheet with ranges of varying numbers of rows, with one row in each range populated by =vlookup from a table in a separate worksheet. The table will be populated with various values for different clients, meaning that several ranges will have #N/A errors at any given time. I would like to b able to delete the specific ranges with #N/A errors via VBA, but haven't been able to do so. The ranges are always from Col A to L, but vary between 2 and 5 rows in height based on fixed data (only one row changes base on vlookup), so I think I will need to reference each individually. Below is one iteration I attempted, working up from the bottom of the worksheet, where he ranges are only two rows. Part of my problem is not knowing if I am referencing the #N/A error properly (I have only used values in the past). Any help would be appreciated.

Sub BlankCategoryDelete()


With Sheets("Review")
.Visible = True
If .Range("A130") = IsError Then
.Range("A130:L131").Delete Shift:=xlUp
End If
If .Range("A128") = IsError Then
.Range("A128:A129").Delete Shift:=xlUp
End If
If .Range("A126") = IsError Then
.Range("A126:L127").Delete Shift:=xlUp
End If
If .Range("A124") = IsError Then
.Range("A124:L125").Delete Shift:=xlUp
End If
If .Range("A122") = IsError Then
.Range("A122:L123").Delete Shift:=xlUp
End If
If .Range("A120") = IsError Then
.Range("A120:L121").Delete Shift:=xlUp
End If

End With

End Sub

 

[JoeMo]

The correct syntax would be:
With Sheets("Review")
If Iserror(.Range("A130")) Then
'rest of code
End With

 

[Rick Rothstein]

Hello,

I have an Excel 2010 worksheet with ranges of varying numbers of rows, with one row in each range populated by =vlookup from a table in a separate worksheet. The table will be populated with various values for different clients, meaning that several ranges will have #N/A errors at any given time. I would like to b able to delete the specific ranges with #N/A errors via VBA, but haven't been able to do so. The ranges are always from Col A to L, but vary between 2 and 5 rows in height based on fixed data (only one row changes base on vlookup), so I think I will need to reference each individually. Below is one iteration I attempted, working up from the bottom of the worksheet, where he ranges are only two rows. Part of my problem is not knowing if I am referencing the #N/A error properly (I have only used values in the past). Any help would be appreciated.

Sub BlankCategoryDelete()


With Sheets("Review")
.Visible = True
If .Range("A130") = IsError Then
.Range("A130:L131").Delete Shift:=xlUp
End If
If .Range("A128") = IsError Then
.Range("A128:A129").Delete Shift:=xlUp
End If
If .Range("A126") = IsError Then
.Range("A126:L127").Delete Shift:=xlUp
End If
If .Range("A124") = IsError Then
.Range("A124:L125").Delete Shift:=xlUp
End If
If .Range("A122") = IsError Then
.Range("A122:L123").Delete Shift:=xlUp
End If
If .Range("A120") = IsError Then
.Range("A120:L121").Delete Shift:=xlUp
End If

End With

End Sub

See if this much shorter macro (replace your macro with it) does what you want...

Sub BlankCategoryDelete()
  Sheets("Review").Range("A120:A130").SpecialCells(xlFormulas, xlErrors).Delete Shift:=xlUp
End Sub

Note: There is no need to unhide the sheet in order for this code to work... if you do want to unhide the sheet, though, then your posted code shows you already know how to do it.

 

[mjayh]

The correct syntax would be:
With Sheets("Review")
If Iserror(.Range("A130")) Then
'rest of code
End With

Thanks! This worked perfectly.

 

[mjayh]

Thanks!

 

[JoeMo]

Thanks! This worked perfectly.

you are welcome - thanks for your reply.

 

[mjayh]

The correct syntax would be:
With Sheets("Review")
If Iserror(.Range("A130")) Then
'rest of code
End With

Thanks your help!