CODEWITHSUNDEEP
vbaformula
rakesh seebaruth
rakesh seebaruth

Reputation: 69

delete numbers from a column

I have the following mumbers in column B

4025015659
4025015660
4025015661
4025015662
-266490.78
-266491.78
-266492.78
-266493.78
0
0
0

I need to the delete the all numbers except negative numbers. Is it possible to have a formula or vba code to do so.

Upvotes: 1

Views: 39

Answers (2)

QHarr
QHarr

Reputation: 84465

It is more efficient to delete in one go and use a With statement/qualify your range

Option Explicit
Public Sub deleteRows()
    Dim unionRng As Range, loopRange As Range, rng As Range
    With Worksheets("Sheet3") '>==Change to correct sheet
        Set loopRange = .Range("B1:B" & .Cells(.Rows.Count, "B").End(xlUp).Row)
        For Each rng In loopRange
            If rng.Value < 0 Then
                If Not unionRng Is Nothing Then
                    Set unionRng = Union(unionRng, rng)
                Else
                    Set unionRng = rng
                End If
            End If
        Next
    End With
    If Not unionRng Is Nothing Then unionRng.EntireRow.Delete
End Sub

Upvotes: 1

Santosh
Santosh

Reputation: 12353

Try this

Sub deleteRows()

    Dim lastRow As Long
    lastRow = Range("B" & Rows.CountLarge).End(xlUp).Row

    For i = lastRow To 1 Step -1
        If Cells(i, "B") < 0 Then
             Cells(i, "B").EntireRow.Delete
        End If
    Next
End Sub

Upvotes: 1

Related Questions