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

chiswickbridge · Feb 21, 2019

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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Col A[/TD]
[TD]Col B[/TD]
[TD]Col C[/TD]
[TD]Col D[/TD]
[TD]Col E[/TD]
[TD]Col F[/TD]
[TD]Col G[/TD]
[TD]Col H[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]560[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]45[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CCC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]453[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DDD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]125[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]452[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EEE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]478[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FFF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.....

chiswickbridge · Feb 21, 2019

Akuini,

THANK YOU,

This works SO Perfect, Its Amazing.....deleted in a Jiffy...

Thank you again n have a Great day.....

[FONT=&quot]Sub a1088408c[/FONT]

Akuini · Feb 21, 2019

chiswickbridge said:
Akuini,

THANK YOU,

This works SO Perfect, Its Amazing.....deleted in a Jiffy...

Thank you again n have a Great day.....

Sub a1088408c

Actually the code needs a minor correction, in case column A isn’t suitable to use to determine the last row of your data set:

Change this line :

Code:

z = Range("A:A").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

to this:

Code:

z = Range("A:H").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

And you're welcome, glad to help, & thanks for the feedback.

chiswickbridge · Feb 21, 2019

Akuini,

WORKS PERFECT...

THANK YOU VERY MUCH.....have a Great Day.

Mike n jjasmith4...thanks to you as well....

jjasmith4 · Feb 22, 2019

Code:

Public Sub DeleteRows()

 
    Dim SmallCells As Range
    Dim Cell As Range
    Dim MinVal As Double
    Dim RemoveIt As Boolean
    
    With Sheet1
        
        MinVal = .Range("H5").Value
    
        With .Range("A10:H10") ' Headers, then all data below them, then each cell in the last column
        With Intersect(.EntireColumn, Sheet1.UsedRange.Offset(.Row))
        For Each Cell In .Columns(.Columns.Count).Cells

  
            Select Case True
            Case IsEmpty(Cell.Value):   RemoveIt = True
            Case Cell.Value < MinVal:   RemoveIt = True
            Case Else:                  RemoveIt = False
            End Select

  
            If RemoveIt Then

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

 
    End With

 
End Sub

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

chiswickbridge

Board Regular

chiswickbridge

Board Regular

Excel Facts

Akuini

Well-known Member

chiswickbridge

Board Regular

jjasmith4

Board Regular

Similar threads

Forum statistics

Share this page

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

chiswickbridge

Board Regular

chiswickbridge

Board Regular

Excel Facts

Akuini

Well-known Member

chiswickbridge

Board Regular

jjasmith4

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock