Delete Rows. Greater than or Equal to

[mayuko]

Hi,

I need some help with Macro

I want to delete all rows that has values in column D with a value
greater than or equal to C.

Any help would be greatly appreciated! Thank you.

 

[BrandonWLH]

give that a shot

Sub test()
    Dim rng As Range
    Set rng = Worksheets("Sheet1").Range("C1")
    Dim x As Integer
    x = 0
    
    Do While Not IsEmpty(rng.Offset(x, 0))
        If rng.Offset(x, 0) > rng.Offset(x, 1) Then
            rng.Offset(x, 0).EntireRow.Delete
        Else
            x = x + 1
        End If
    Loop
    
End Sub

 

[Dazzawm]

Hi Brandon. I am learning VBA and I was trying to see if I could come up with a solution for this question (not to submit it just see if I was anywhere near), so could you look at my code and see where I am going wrong or am I miles away!. Thanks

Sub test()
Dim LR As Integer
Dim A As Integer
Dim B As Integer
Range("D1").Select
LR = ActiveCell.CurrentRegion.Rows.Count
B = Selection.Offset(-1, 0)
For A = 1 To LR
If Selection.Value >= B Then
Selection.EntireRow.Delete
Else
Selection.Offset(1, 0).Select
End If
Next A
End Sub

 

[mayuko]

Thank you for guys

Brandon... I ran it, but it didn't do anything on my sheet...

dazwm... When I ran your code, it game me error saying "400."

 

[mirabeau]

dazwm,

Maybe Brandon isn't here. But looking at your code there's one obvious source of error.

Consider the two lines in red

Sub test()
Dim LR As Integer
Dim A As Integer
Dim B As Integer
Range("D1").Select
LR = ActiveCell.CurrentRegion.Rows.Count
B = Selection.Offset(-1, 0)
For A = 1 To LR
If Selection.Value >= B Then
Selection.EntireRow.Delete
Else
Selection.Offset(1, 0).Select
End If
Next A
End Sub

You select the top cell, D1, in a column with the first red line, then you try to offset that one row up.

This tells the VBA that you want to define B as the value of a cell outside the worksheet, which the VBA code really doesn't like doing ...

 

[mirabeau]

Hi,

I need some help with Macro

I want to delete all rows that has values in column D with a value
greater than or equal to C.

Any help would be greatly appreciated! Thank you.

mayuko,

Would you like to try this code (on some test data first) and see if it works for you

Sub delrows()
Dim col As Range, c As Range, nr&
Dim u(), k&, e, p&
Set col = Range("D:D")
Set c = Cells(1, Columns.Count)
nr = col(Rows.Count).End(3).Row
ReDim u(1 To nr, 1 To 1)
For Each e In col.Resize(nr).Value
    k = k + 1
    If e >= C Then u(k, 1) = 1: p = p + 1
Next
c.Resize(nr) = u
Cells(1, 1).Resize(nr, Columns.Count).Sort c, 1
Cells(1, 1).Resize(p, Columns.Count).Delete
End Sub

Although it occurs to me that your C may not be a numerical value in its own right, as that code assumes, but rather some numerical value(s) located somewhere in column C.

Which do you mean? The code is easily modified to do either.

 

[Dazzawm]

I actually did pretty good then. What I got wrong was my offset (I keep getting them mixed up!)

Instead of this

B = Selection.Offset(-1, 0)

It should of been

B = Selection.Offset(0, -1)

then it works ok...

 

[BrandonWLH]

Thank you for guys

Brandon... I ran it, but it didn't do anything on my sheet...

I think I know why. 1st row is getting deleted and then the rng goes out of scope and its always empty as a result so it kick out of the loop. Duplicate the set rng line right under the delete line.

 

[mayuko]

mayuko,

Would you like to try this code (on some test data first) and see if it works for you

Sub delrows()
Dim col As Range, c As Range, nr&
Dim u(), k&, e, p&
Set col = Range("D:D")
Set c = Cells(1, Columns.Count)
nr = col(Rows.Count).End(3).Row
ReDim u(1 To nr, 1 To 1)
For Each e In col.Resize(nr).Value
    k = k + 1
    If e >= C Then u(k, 1) = 1: p = p + 1
Next
c.Resize(nr) = u
Cells(1, 1).Resize(nr, Columns.Count).Sort c, 1
Cells(1, 1).Resize(p, Columns.Count).Delete
End Sub

Although it occurs to me that your C may not be a numerical value in its own right, as that code assumes, but rather some numerical value(s) located somewhere in column C.

Which do you mean? The code is easily modified to do either.

mirabeau
Thanks, mirabeau. I am pretty sure that C is real numbers... That's what you meant, right?? I ran it then it deleted everything. I am very new to VBA, so I am so sorry I don't know what happened (;_

 

[mayuko]

I think I know why. 1st row is getting deleted and then the rng goes out of scope and its always empty as a result so it kick out of the loop. Duplicate the set rng line right under the delete line.

BrandonWLH
Thank you, Brandon. When I changed and ran, it gave me "duplicate" error. I think I am not understanding it right because I am not an English speaker, so if you could post the whole code, I would really appreciate it. Thank you again.