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

C

chiswickbridge

Board Regular
Joined
Feb 2, 2013
Messages
130
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.....
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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

S
  • Solved
Delete Row if value in column C is a duplicate
Replies
8
Views
247
igold
igold
P
  • Question Question
ListBox Click to textbox & delete row
Replies
0
Views
592
pure vito
P
I
  • Solved
Delete entire row based off cell value ignoring 2nd row
Replies
6
Views
340
ItalianPlatinum
I
H
  • Solved
Macro to Delete Rows Where there is a value in Col H from row 2 onwards
Replies
9
Views
451
mumps
mumps
R
  • Solved
Failed in order to derive Row number or Value in cell of particular Row Using Vlookup and Index... Match
2
Replies
10
Views
626
RapchikM
R

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

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