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
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