clear contents of a set range of cells based on a criteria

Graham JN

New Member
Joined
Apr 15, 2018
Messages
13
i have a sheet with data in B5:G20

I have data in column B which is either a 0 or a number greater than 0 - If the contents of this cell is 0

I would like it to clear the contents of the ROW within the range B to G only (not the entire row only B To G)
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Does this do what you want...
Code:
[table="width: 500"]
[tr]
	[td]Sub DeleteIfZeroInColB()
  With Columns("B")
    .Replace 0, "#N/A", xlWhole, , , , False, False
    Intersect(.SpecialCells(xlConstants, xlErrors).EntireRow, Columns("B:G")).ClearContents
  End With
End Sub[/td]
[/tr]
[/table]
 
Upvote 0
thank you for your help is there a way it will consider rows 5 to 20 and no other rows
We just need to change the object of the With statement from Columns("B") to Range("B5:B20")...
Code:
[table="width: 500"]
[tr]
	[td]Sub DeleteIfZeroInColB()
  With Range("B5:B20")
    .Replace 0, "#N/A", xlWhole, , , , False, False
    Intersect(.SpecialCells(xlConstants, xlErrors).EntireRow, Columns("B:G")).ClearContents
  End With
End Sub[/td]
[/tr]
[/table]
 
Last edited:
Upvote 0
thank you for your help is there a way it will consider rows 5 to 20 and no other rows so it only clears within the row range of B5 to G20
 
Upvote 0
cheeky i know but it works great on my test sheet, however on my actual sheet it has a formula which picks up a cell value, with the formula in there which displays 0 it fails, if i change the formula to 0 hey presto, is there a way around this so the macro ignores the formula and sees the number the formula is producing.
 
Upvote 0
cheeky i know but it works great on my test sheet, however on my actual sheet it has a formula which picks up a cell value, with the formula in there which displays 0 it fails, if i change the formula to 0 hey presto, is there a way around this so the macro ignores the formula and sees the number the formula is producing.
Are you saying all of the cells in range B5:B20 contain formulas? If so...

Is the formula supposed to be deleted from the cell along with the data when the formula is display 0?

Are there any formulas in the cells in range C5:G20 and, if so, are they to be deleted also?
 
Upvote 0
Thanks for staying with me here, in column B5 to B20 it is picking up a cell from another sheet (qty) but I have use a cell reference =Y10 so in column B I can see the correct number such as 0,or 1 or 2 etc
However when the vba runs it does not recognise the cell reference as 0 so it does not delete.

If i replace the cell "reference 0 (Y10)" with an actual 0 the vba works perfect.

So basically the vba delete 0 does not recognise the cell reference Y10 which shows a 0 which it is picking up from another sheet

Did I explain ok
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

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