vba code find all zero values and delete entire row

esvetalalala

New Member
Joined
May 29, 2012
Messages
20
hello,
i am trying to create a report that takes values from a tab. I need to remove lines with zero values (and positive figures less than 0) in the grand total (last column). Most of the numbers are not formatted and not to 2 dec points.
this is the relevan part of the code:

....
'formatting
wsDest.Select

Columns("A:z").EntireColumn.AutoFit

royresult.Activate

Set rg = range("B:N")
rg.Select

Selection.Value = Format(Cells("#.00"))

End With

royresult.Activate


' to remove rows with zero in grand total

' 1st find the last columnt

Dim LastColumn As Integer

If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Columns.

LastColumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

Cells(1, LastColumn).Select
Set lc = Selection
r = LastColumn

End If

' 2nd filter all zero values and delete entire row

Dim LastRow As Long, n As Long
LastRow = range("A10000").End(xlUp).Row
For n = LastRow To 1 Step -1
Cells(n, r).Value = vr
If 0 >= vr > -0.1 Then Cells(n, r).EntireRow.Delete
Next n

End Sub

the issue is it doesn't format figures to two decimals and then remove zeros.. and shows some values as ".00" where it is in fact "0.002234111" . i tried many formatting options and could never arrive to removing the zero and values less than zero. Wonder if anyone can help as i'm completely stuck...
I'm using excel 2010
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Ok I think this might help:
Code:
Sub test()
Dim Content As String
Dim Col As String
Range("B2").Select
Content = ActiveCell.Value
Col = ActiveCell.Column
Do Until Col = 15
Do Until Content = ""
ActiveCell = WorksheetFunction.Round(ActiveCell.Value, 2)
ActiveCell.Offset(1, 0).Select
Content = ActiveCell.Value
Loop
Col = ActiveCell.Column
Cells(2, Col + 1).Select
Col = ActiveCell.Column
Content = ActiveCell.Value
Loop
End Sub
It is assuming that the data you want to 2 decimal places will start in cell B2 and will end in column N. It will go through and change the value not the format so you should be able to input before your format command in the macro and hopefully it will change the values for you.

After that your macro should then be able to pick up all the 0.00 values as they will be actual zero's not just made to look that way.

Please test it as I have only tried this on a small sample of data.
 
Upvote 0
hello Soggy, thank you for the code... i have tried and still cannot get it to do the job. i have some figures two rows shown as below, when i format them using excel button in the ribbon they show as zeros. is there something wrong with the numbers format?

-0.001650-1.77636E-15-0.004456256-7.10543E-15
0.0032125010000

<colgroup><col span="5"></colgroup><tbody>
</tbody>
 
Upvote 0
It's because the command is to round so if it is less than 0.005 it will round to zero as it doesn't automatically round one way or the other.

If you want to round up then WorkSheetFunction.RoundUp should do that and these will go to 0.01 but it will also mean all the other numbers will also roundup.
 
Upvote 0
Thank you! it seems the problem is in my zero deletion code.. i'm trying to resolve it and will post the code here once found
 
Upvote 0
the deletion code fixed:
Dim LastRow As Long, n As Long

LastRow = range("A65536").End(xlUp).Row
For n = LastRow To 1 Step -1
If Cells(n, r).Value = 0 Then Cells(n, 2).EntireRow.Delete

Next n
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

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