How do i santise a Range

[rhino4eva]

I have a WS with 3 column 24 row that is fully populated with formulas that are
dependant on data from other WS
However this sheet's results are variable and only result if they are not in Error

This leaves several rows down to 24 that APPEAR to be blank but STILL have a formula in them

This sheet is then used to another bit of software but the area with Formulas still in report as "0"

Has anyone got a stylish piece of code that looks at a the range a2,c24 and removes either unused formulas or "0"

 

[gaz_chops]

Are the formulas returning a zero?
If so change it to return ""

 

[rhino4eva]

have tried that but the 3rd party software is very picky and I would like to delete and get rid of everything apart from visible cells

 

[Fluff]

Does your formula have an IFERROR or IF(ISERROR?

 

[rhino4eva]

=IFERROR(ROUND(GETPIVOTDATA("CALC",Tabulate!$N$1,"Lab number",A2,"MONO","A6"),0),0)

 

[Fluff]

In that case if you remove the IFERROR you could use something like

Sub rhino4eva()
   Range("A2:C24").SpecialCells(xlFormulas, xlErrors).ClearContents
End Sub

 

[rhino4eva]

Fluff you strike again... I bastardised your previous solution with your ne special filters and hey presto ... job done
Dim Rng As Range
For Each Rng In Range("B:J").SpecialCells(xlFormulas, xlErrors).Areas
Intersect(Rng.EntireRow, Range("A:J")).Delete shift:=xlUp
Next Rng
Sheets("BionumericsImport").UsedRange.Select

 

[Fluff]

Glad you sorted it & thanks for the feedback