Delete Rows when cell value is Null or less than a number

[chiswickbridge]

I have a data base in columns A:H max to 99999 rows, commencing from Row 10 as Header and Row 11 to 99999 as data

Col A	Col B	Col C	Col D	Col E	Col F	Col G	Col H
AAA							560
BBB							45
CCC							453
DDD							125
							452
EEE							478
							12
FFF							15

<tbody>
</tbody>

I want to delete Rows,
First Criteria - when Col A is Null value
Second Criteria when Col H is less than 99 or a certain number that I specify in Cell H5

this below vba...runs very slow as the rows are about 85000

Sub delrow() 'DELETES ANY ROW IN COLUMN "H" THAT IS BLANK
Dim i As Long
For i = Cells(Rows.Count, "A").End(xlUp).Row To 10 Step -1
If Range("H" & i).Value < 99 Then Rows(i).Delete
Next i
End Sub

Can I have a vba, that sorts Col A from A to Z, and at the first instance of a Null value....deletes everthing below it

and then sorts Col H from Highest to Lowest, and at the first instance, when cell in H Column is below 99...it deletes everyhing below it.

Thanks.....

 

[jjasmith4]

Public Sub DeleteRows()


    Dim SmallCells As Range
    Dim Cell As Range
    Dim MinVal As Double
    
    With Sheet1
        
        MinVal = .Range("H5").Value
    
        With .Range("H10")              ' The header in column H
        For Each Cell In .Range(.Offset(1, 0), .End(xlDown).End(xlDown).End(xlUp)).Cells
        
            If Cell.Value < MinVal Then ' Here's one to delete


                If SmallCells Is Nothing _
                Then Set SmallCells = Cell _
                Else Set SmallCells = Union(Cell, SmallCells)
                                        ' Gather the cells with small values first
            End If
        
        Next
                                        ' Delete all small-cells rows in one shot
        If Not (SmallCells Is Nothing) Then SmallCells.EntireRow.Delete


    End With


End Sub

 

[chiswickbridge]

Hi ...

It does not work...

Deleting every row after the 19th row....

 

[jjasmith4]

? There should be an "End With" statement directly under the "Next" statement.

 

[Michael M]

Are the cells in Col "A" actually blank or have a formula that returns a Blank ?

AND

it deletes everyhing below it.

....EVERYTHING ???

 

[chiswickbridge]

Michael,

Yes....Some cells are actually blank, as the data is imported from a text file

 

[Akuini]

Maybe this:

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1088408b()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1088408-delete-rows-when-cell-value-null-less-than-number.html[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] r [COLOR=Royalblue]As[/COLOR] Range, c [COLOR=Royalblue]As[/COLOR] Range
[COLOR=Royalblue]Dim[/COLOR] z [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], x [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], flag [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Boolean[/COLOR]

Application.ScreenUpdating = [COLOR=Royalblue]False[/COLOR]
x = Range([COLOR=brown]"A:A"[/COLOR]).Find([COLOR=brown]"*"[/COLOR], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range([COLOR=brown]"A10:A"[/COLOR] & x).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

x = Range([COLOR=brown]"A:A"[/COLOR]).Find([COLOR=brown]"*"[/COLOR], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
[COLOR=Royalblue]Set[/COLOR] r = Range([COLOR=brown]"A10:H"[/COLOR] & x)
r.Sort Key1:=r.Cells([COLOR=crimson]1[/COLOR], [COLOR=crimson]8[/COLOR]), order1:=xlDescending, Header:=xlYes

va = Range([COLOR=brown]"H1:H"[/COLOR] & x)
flag = [COLOR=Royalblue]False[/COLOR]
    
    [COLOR=Royalblue]For[/COLOR] i = UBound(va, [COLOR=crimson]1[/COLOR]) [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]11[/COLOR] [COLOR=Royalblue]Step[/COLOR] -[COLOR=crimson]1[/COLOR]
    
        [COLOR=Royalblue]If[/COLOR] va(i, [COLOR=crimson]1[/COLOR]) >= Range([COLOR=brown]"H5"[/COLOR]) [COLOR=Royalblue]Then[/COLOR] flag = [COLOR=Royalblue]True[/COLOR]: [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]For[/COLOR]
    
    [COLOR=Royalblue]Next[/COLOR]

    [COLOR=Royalblue]If[/COLOR] flag = [COLOR=Royalblue]True[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        Range(Cells(i + [COLOR=crimson]1[/COLOR], [COLOR=brown]"A"[/COLOR]), Cells(x, [COLOR=brown]"H"[/COLOR])).ClearContents
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]

Application.ScreenUpdating = [COLOR=Royalblue]True[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
[/FONT]

 

[chiswickbridge]

Akuini,

It gives a Runtime error "1004"
No cells were found

fiollowing command gets highlighted as yellow
[FONT=&quot]Range("A10:A" & x).SpecialCells(xlCellTypeBlanks).EntireRow.Delete[/FONT]

 

[Akuini]

Akuini,

It gives a Runtime error "1004"
No cells were found

fiollowing command gets highlighted as yellow
Range("A10:A" & x).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Actually it works on your sample. Maybe what you have isn't really blank.
Try this:

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1088408c()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1088408-delete-rows-when-cell-value-null-less-than-number.html[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] r [COLOR=Royalblue]As[/COLOR] Range, c [COLOR=Royalblue]As[/COLOR] Range
[COLOR=Royalblue]Dim[/COLOR] z [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], x [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], flag [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Boolean[/COLOR]

Application.ScreenUpdating = [COLOR=Royalblue]False[/COLOR]
z = Range([COLOR=brown]"A:A"[/COLOR]).Find([COLOR=brown]"*"[/COLOR], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
[COLOR=Royalblue]Set[/COLOR] r = Range([COLOR=brown]"A10:H"[/COLOR] & z)
r.Sort Key1:=r.Cells([COLOR=crimson]1[/COLOR], [COLOR=crimson]1[/COLOR]), order1:=xlAscending, Header:=xlYes
x = Range([COLOR=brown]"A:A"[/COLOR]).Find([COLOR=brown]"*"[/COLOR], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range(Cells(x + [COLOR=crimson]1[/COLOR], [COLOR=brown]"A"[/COLOR]), Cells(z + [COLOR=crimson]1[/COLOR], [COLOR=brown]"H"[/COLOR])).ClearContents
[COLOR=Royalblue]Set[/COLOR] r = Range([COLOR=brown]"A10:H"[/COLOR] & x)
r.Sort Key1:=r.Cells([COLOR=crimson]1[/COLOR], [COLOR=crimson]8[/COLOR]), order1:=xlDescending, Header:=xlYes

va = Range([COLOR=brown]"H1:H"[/COLOR] & x)
flag = [COLOR=Royalblue]False[/COLOR]
    
    [COLOR=Royalblue]For[/COLOR] i = UBound(va, [COLOR=crimson]1[/COLOR]) [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]11[/COLOR] [COLOR=Royalblue]Step[/COLOR] -[COLOR=crimson]1[/COLOR]
    
        [COLOR=Royalblue]If[/COLOR] va(i, [COLOR=crimson]1[/COLOR]) >= Range([COLOR=brown]"H5"[/COLOR]) [COLOR=Royalblue]Then[/COLOR] flag = [COLOR=Royalblue]True[/COLOR]: [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]For[/COLOR]
    
    [COLOR=Royalblue]Next[/COLOR]

    [COLOR=Royalblue]If[/COLOR] flag = [COLOR=Royalblue]True[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        Range(Cells(i + [COLOR=crimson]1[/COLOR], [COLOR=brown]"A"[/COLOR]), Cells(x + [COLOR=crimson]1[/COLOR], [COLOR=brown]"H"[/COLOR])).ClearContents
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]

Application.ScreenUpdating = [COLOR=Royalblue]True[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[Michael M]

@Akuini
I'm guessing NBSP's if it's an imported text file...