delete string numbers

[somenoob]

Hi everyone, i have a problem. i have the following codes to delete the entire row if in column I, there is a value 0.

Sub DeleteZeroRows()
Dim Zeroes As Range
'assuming that column I is the one that needs to be checked
'"I" states that it is in column I. change to your desired column alphabet.
Const AccountColumn As String = "I"
With Columns(AccountColumn)
'number 0s is all deleted (rows)
.Replace "0", "=0", xlWhole
On Error Resume Next
.SpecialCells(xlFormulas).EntireRow.Delete
End With
End Sub

However, when i retrieve my data from the add-ins, the data in column I is like this: '0. It is not purely 0.

so when i run the delete macro, it did not delete the row. But rather, it changed the data to '=0. How do i change the code so that it can delete the row?

please Help. Thanks

 

[somenoob]

also the code deletes the entire row. But i do not want it to delete the values in column A. how do i do that? To be Exact, i want it to delete values in column B to I

 

[GTO]

Hi there,

First, in a junk copy of your wb, try ridding the prefix characters that are coming with the values for whatever reason. Something like:

    With ThisWorkbook
        .Worksheets("Sheet6").Range("I:I").Value = .Worksheets("Sheet6").Range("I:I").Value
    End With

If that changes them back to numbers, then I think the rest of the code works, save we want to not delete the vals is Col A.

 

[Rick Rothstein]

Some questions first (please answer them all as they are all important)...

1) Do you have any formulas in the range B:I? What about outside of that range?

2) Does all your data have apostrophes in front of them (both numbers and text)? If so, do you want all of the apostrophes removed? If so, from the range B:I only or throughout the sheet (even in columns outside of B:I)?

 

[somenoob]

oh thanks it worked. I have another question. If i want to delete negative values as well how do i do that?

i have tried like e.g logic < 0.

i deletes values lesser than 0 as in -1.
but it does not delete values in decimal like -0.1

 

[Rick Rothstein]

oh thanks it worked. I have another question. If i want to delete negative values as well how do i do that?

You need a different approach for that. Assuming the questions I asked were answered based on your responses to GTO (namely, there are no formulas and all data can have the apostrophe removed), give this macro a try...

Sub DeleteZeroRows()
  Dim LastRow As Long, UnusedColumn As Long, ZeroOrLess As Range
  LastRow = Columns("B:I").Find(What:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
  UnusedColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
                 SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
  ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
  With Cells(1, UnusedColumn).Resize(LastRow)
    .FormulaR1C1 = "=IF(COUNTIF(RC2:RC9,""<=0""),""X"","""")"
    .Value = .Value
    Intersect(Columns("B:I"), .SpecialCells(xlConstants).EntireRow).ClearContents
    .Clear
  End With
End Sub

 

[somenoob]

thanks it works. i have another code with the same problem.

Dim threshold As Long
threshold = 0.5
Dim negativeThreshold As Long
negativeThreshold = 0
Dim logic As String
logic = (ws.Range("C" & x) = "check") And Val(ws.Range("D" & x))

If logic > threshold Or logic < negativeThreshold Then
ws.Range("E" & x).Value = "->Chk_" & counter
counter = counter + 1
Else
ws.Range("E" & x).Value = ""
End If

Next x

i want to check for values lesser than 0. As in negative values. But it can get -1. but not -0.1

please help