Delete rows with unused formulas

[TycStyle]

Hi all,

I have read many threads on deleting rows with certain (or no) values in cells. What I am trying to do is following:

I have a sheet containing formulas, when data is copied into the sheet (number of rows differs every time), I end up with good data for the pasted rows and the rest of rows which contain the default values and formulas (column A contains #VALUE!). What I want the script to do is only delete those remaining rows and end up with the needed data.
Because the #VALUE result is not regognized as a proper value, I haven't been able to solve this.....

Any help is appreciated.

Thanks in advance.

 

[texasalynn]

why not use auto filter and filter for #VALUE
delete those rows

 

[schielrn]

Welcome to the Board!

Try recording a macro using autofilter. Apply a filter and filter for #VALUE and then delete those rows.

Hope that helps.

 

[TycStyle]

Hi both,

Thanks for the suggestion. I've tried that, but when I manually record the macro, it will use the values from the used data when selecting the rows.
F.e. in the first used data (for the recording) the first #VALUE! could be in row 220, but when new data is used it might be in row 200, which automatically gives a problem as the macro has recorded it should delete starting from row 220 and not 200.

 

[TycStyle]

What I was thinking, how could I user ERROR.TYPE in this case??

 

[TycStyle]

Ok,

What I have done now to create a solution:
This results in a Type Mismatsch Runtime Error 13......... Anyone?


Sub Macro2()
'
'
'
Columns("A:A").Select
Selection.Copy
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = "@"
Range("a:a").Select
For Each cell In Selection
If cell.Value = "#VALUE!" Then
Selection.EntireRow.Delete
End If
Next cell
Range("a1").Select
Selection.NumberFormat = "yyyymmdd"
End Sub

 

[schielrn]

You would need something like:

If iserror(cell.Value) Then

But then again that will delete all errors, even #N/A and #NUM just to name a few.

Can you post the code you recorded with the autofilter. It can probably be cleaned up to be more dynamic.

Hope that helps.

 

[TycStyle]

This is the auto filter solution as tried earlier:

Sub DeleteErrorValues()
'

Rows("1:1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="#VALUE!"
Rows("221:221").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=0
Selection.Delete Shift:=xlUp
Selection.AutoFilter
End Sub

 

[schielrn]

Try this:

Sub DeleteErrorValues()
'
 
Rows("1:1").AutoFilter
Rows("1:1").AutoFilter Field:=1, Criteria1:="#VALUE!"
Range("A2:A" & cells(rows.count,1).end(xlup).row).specialcells(xlCellTypeVisible).entirerow.Delete Shift:=xlUp
Rows("1:1").AutoFilter 
End Sub

Note that this is untested, so I would advise making a back up copy before trying it.

Hope that helps.

 

[TycStyle]

All that did in the end is delete the headerrow in the process. No #VALUE! rows where deleted....