How do i santise a Range

rhino4eva

Active Member
Joined
Apr 1, 2009
Messages
258
Office Version
  1. 2010
Platform
  1. Windows
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"
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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
 
Upvote 0
Does your formula have an IFERROR or IF(ISERROR?
 
Upvote 0
=IFERROR(ROUND(GETPIVOTDATA("CALC",Tabulate!$N$1,"Lab number",A2,"MONO","A6"),0),0)
 
Upvote 0
In that case if you remove the IFERROR you could use something like
Code:
Sub rhino4eva()
   Range("A2:C24").SpecialCells(xlFormulas, xlErrors).ClearContents
End Sub
 
Upvote 0
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
 
Upvote 0
Glad you sorted it & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top