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

[Graham JN]

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)

 

[Rick Rothstein]

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

So, cells B5:B20 have formulas in them. Given that, you did not answer the two follow up questions that I asked in Message #9 . Here are the questions again...

Is the formula (in cells B5:B20) supposed to be deleted from the cell along with the data when that formula is displaying 0?

Are there any formulas in the cells in range C5:G20 and, if so, are they to be deleted also?

 

[Graham JN]

So, cells B5:B20 have formulas in them. Given that, you did not answer the two follow up questions that I asked in Message #9 . Here are the questions again...

Is the formula (in cells B5:B20) supposed to be deleted from the cell along with the data when that formula is displaying 0?

Are there any formulas in the cells in range C5:G20 and, if so, are they to be deleted also?

There are formulas in c5 to g20 but yes it can all be deleted
Also the formula in B column can be deleted.

so if any cell in column B is showing 0 (because it has pick up the Qty elsewhere on sheet) then nothing is required and everything can be deleted for that row

so B5 is showing 0 (which is actual picking up the value from y5) then the entire row can be deleted b5 to g5

your vba works perfect it just seems not to see the 0 as a 0 because it is a formula picking up the qty from elsewhere in the work book

sorty if I am not explaining this well thank you so much for staying with me.

 

[Rick Rothstein]

There are formulas in c5 to g20 but yes it can all be deleted
Also the formula in B column can be deleted.

so if any cell in column B is showing 0 (because it has pick up the Qty elsewhere on sheet) then nothing is required and everything can be deleted for that row

so B5 is showing 0 (which is actual picking up the value from y5) then the entire row can be deleted b5 to g5

Delete the macro I gave you earlier and see if this macro works for you instead...

[table="width: 500"]
[tr]
	[td]Sub DeleteIfZeroInColB()
  Dim UnusedCol As Long
  UnusedCol = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious, , , False).Column + 1
  Application.ScreenUpdating = False
  With Range(Cells(5, UnusedCol), Cells(20, UnusedCol))
    On Error Resume Next
    .Value = Evaluate("IF(" & Intersect(.EntireRow, Columns("B")).Address & "=0,""X"","""")")
    Intersect(.SpecialCells(xlConstants).EntireRow, Columns("B:G")).ClearContents
    .Clear
    On Error GoTo 0
  End With
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

 

[Graham JN]

Perfect and thanks, I love one day to understand one day what the vba solution was