CODEWITHSUNDEEP
excelvba
Iftakhar Ahmad
Iftakhar Ahmad

Reputation: 1

Convert a range's value from text to Number

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

Upvotes: 0

Views: 892

Answers (2)

Iftakhar Ahmad
Iftakhar Ahmad

Reputation: 1

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

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60199

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

Upvotes: 1

Related Questions