Convert a range's value from text to Number

Question

I want to delete negative values in a range in excel. I have a code that deletes but not all the values.

I got to know that I should first change the value to numeric type. I have the below code in which I have tried to do so with cDec and Convert.ToInt32 but not successful. I am new to vba, I don't know much about its data types. Your help will be highly appreciable:

Sub Button1_Click()
    Dim ws As Worksheet
    Dim i As Integer

    i = 1

    Set ws = Sheets("Recovered_Sheet1")
    ws.Activate

    Dim r As Excel.Range

    For Each r In Range("A1:A250").Rows
        If Not IsEmpty(ActiveCell.Value) Then
            'move to the row below
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Value = CDec(ActiveCell.Value)
        End If
    Next r

    Do While Cells(i, 1).Value <> ""
        If Cells(i, 1) < 0 Then
            Cells(i, 1).EntireRow.Delete
        End If
        i = i + 1
    Loop
End Sub

http://stackoverflow.com/questions/20192371/vba-conver-text-number-multiplying-by-1 — Siddharth Rout, Feb 07 '14 at 20:18
http://stackoverflow.com/questions/21627250/excel-vba-convert-string-entered-in-user-form-to-number — Siddharth Rout, Feb 07 '14 at 20:18

Ron Rosenfeld · Answer 1 · 2014-02-08T13:38:17.533

1

Here is one way of doing that. Note that when deleting rows, you should work from the bottom up. Also, you don't need to change the Excel data type before running this macro, unless you have some other reason to do so (in which case there are more efficient methods than going cell by cell).

Edit Since text and blanks will return False with .Value < 0, there's no need to test anything else.

Option Explicit
Sub DelNegNumRows()
    Dim I As Long
For I = 250 To 1 Step -1
    With Cells(I, 1)
        If .Value < 0 Then
            .EntireRow.Delete
        End If
    End With
Next I
End Sub

Depending on the characteristics of your range, you may not need to check all 250 rows (although if that is the size of your database, you won't perceive a speed advantage to making the range smaller). For example, if all of your rows with data are non-blank, you can do something like:

lastrow = cells(1,1).end(xldown).row

or, if there might be blanks, and you want to find the last row in column A that has any data, something like:

lastrow = cells(rows.Count,1).end(xlup).row

You could then cycle, in the macro above:

for I = lastrow to 1 step -1

edited Feb 08 '14 at 13:38

answered Feb 07 '14 at 21:03

Ron Rosenfeld

53,870
7
28
60

BTW: If you wish, you can use Autofilter as well or delete the row outside the loop. Your code will be much faster. – Siddharth Rout Feb 08 '14 at 11:58
Since the range appears to be only 250 rows, I did not go into the "faster" techniques. I would ordinarily also turn off screen updating, calculation, etc. One issue I think with AutoFilter is that you can't use a number filter when the numbers are text; and I don't think you can delete non-contiguous "entire rows", so I would be left with at least looping through the "areas". Also, specialcells sees numbers that are stored as text, as textvalues, so you can't use that method either to winnow down the range. But if the range were larger, I'd read it into an array, and test the values in VBA – Ron Rosenfeld Feb 08 '14 at 12:12
`1` True. You can use ".Formula = .Value" method to convert it into number and then use Autofilter `2` You can delete non contiguous rows. I have answered several questions where I have demonstrated that. – Siddharth Rout Feb 08 '14 at 12:18
1

[THIS](http://stackoverflow.com/questions/20077945/delete-cells-in-an-excel-column-when-rows-0/20078126#20078126) uses UNION so that you don't have to reverse loop. `2` [THIS](http://stackoverflow.com/questions/11767277/excel-vba-if-cell-is-an-integer-delete-the-entire-row) uses Autofilter to delete rows. In fact it was has one method which uses Union. – Siddharth Rout Feb 08 '14 at 12:22
I thought I had tried the UNION method in the past. Obviously I had made an error in trying to implement that. Thanks for getting me to check it out again. With regard to changing the numbers to values to use the autofilter, you also have to change the formatting of the cell if the underlying format is text. – Ron Rosenfeld Feb 08 '14 at 12:57
`you also have to change the formatting of the cell if the underlying format is text.` True :) It's already mentioned that in the link under the question :) – Siddharth Rout Feb 08 '14 at 13:31

score 0 · Answer 2 · edited Aug 25 '15 at 05:56

0

Sub Button1_Click()
Dim I As Long
Dim lastrow As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For I = lastrow To 1 Step -1
    With Cells(I, 2)
        If .Value < 0 Then
            .EntireRow.Delete
        End If
    End With
Next I
End Sub

edited Aug 25 '15 at 05:56

Cary Bondoc

2,923
4
37
60

answered Feb 11 '14 at 16:13

Iftakhar Ahmad

1
2
5

Convert a range's value from text to Number

2 Answers2