VBA Code to Delete Rows with zero or Blank values

Question

Dear Sir,

I have an excel file with worksheet as test4 , wheerein i have a product name in Column A and quantity in Column B.

There may cases wherein there will zero values for some fields in Columb B like B2= 0, B6=0.

I want to write VBA code which detects that row with quantity as zero and delete entire row here.

Is that possible now ?

Answer 1

I want you to write VBA code which detects that row with quantity as zero and delete entire row here.

Sub blah()
Dim rngToDelete As Range
With Sheets("test4")
For Each cll In Intersect(.UsedRange, .Columns("B")).Cells
If cll.Value = 0 Or Len(Trim(cll.Value)) = 0 Then
If rngToDelete Is Nothing Then Set rngToDelete = cll Else Set rngToDelete = Union(rngToDelete, cll)
End If
Next cll
If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete
End With
End Sub

Answer 2

It worked well.

Another query is if I have first column as date , second as product code and third as quantity here, I want to give user drop-down selector in another cell say G1 which can enable user to select the date , once date is selected, then logic should sum the quantities for a particular product code uptill that date looking into the table given.

So say in a table , A column is Date column which has chronological dates, B is Product code and C is Quantity and G1 is drop-down selector for the date , then first how to create drop-down selector and also how to write above logic to get sum of quantities uptill particular dates ?

Answer 3

Dear Sir,

Firstly, I wanted to create new query for Microsoft support community but when i go and select product, it is saying no data and that is the reason, I am not able to post new query here. Can you please help me resolve this issue for my account ??

Secondly, I have a query similar to above query.

I have a data as below for product codes, quantity and areas here.

So it is workbook named book1 and worksheet as sheet 1 say.

I want to transfer this data selectively using click of a button to book2 and sheet 1 selectively. here sometimes, some product quantities may be zero also.

How to do this ?

In book2, I have same data and columns but sequence of areas, product codes is changed here. So what i want to do here is that transfer the data to book2 and delete any rows in book2 for which there is zero value in book1 .

Book2 will be something like this

How can I achieve this. Please help.

I should get a macro button on book1 which wehn trigerred will transfer the data to book2 and also delete rows in book2 for which there is zero value in book1.

Please advice.

Also i should be able to select file name for book2 as user changes file name for book2 frequently.