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

C

chiswickbridge

Board Regular
Joined
Feb 2, 2013
Messages
126
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 ACol BCol CCol DCol ECol FCol GCol H
AAA560
BBB45
CCC453
DDD125
452
EEE478
12
FFF15

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

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
Code: 
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
 
Upvote 0
Are the cells in Col "A" actually blank or have a formula that returns a Blank ?

AND

it deletes everyhing below it.
Click to expand...
....EVERYTHING ???
 
Last edited:
Upvote 0
Maybe this:

Code: 
[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]
 
Upvote 0
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]
 
Upvote 0
chiswickbridge said:
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
Click to expand...

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

Code: 
[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]
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Macro to Delete Rows Where there is a value in Col H from row 2 onwards
Replies
9
Views
226
mumps
mumps
D
  • Solved
Find First Instance of Cell Value and Delete Corresponding Entire Row
Replies
5
Views
337
David04Ruiz
D
K
  • Solved
Move and delete row when double clicking on cell
Replies
4
Views
324
smozgur
smozgur
H
  • Question
Macro to Delete Dates in Col A less than Date in cell H1
Replies
2
Views
232
howard
H
N
  • Question
Value Return if less than Zero
Replies
5
Views
353
Flashbond
F

Forum statistics

Threads
1,203,047
Messages
6,053,197
Members
444,645
Latest member
mee siam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top