MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

select cells based on 3 criteria and delete rows

  Asked By: Sebastion    Date: Mar 01    Category: MS Office    Views: 850

I have a sheet with 5 columns, I will like to select Column D and each time the
number 25, 15 or 16 appears, the macro will delete the rows with those numbers.



5 Answers Found

Answer #1    Answered By: Mona Wagner     Answered On: Mar 01

Try this:

Sub DelSomeRows()
DelRowsWith (15)
DelRowsWith (16)
DelRowsWith (25)
End Sub

Sub DelRowsWith(x)
Set MyRange = Intersect _
(ActiveSheet.Range("D:D"), ActiveSheet.UsedRange)
For Each MyCell In MyRange
If MyCell.Value = x Then
MyCell.EntireRow.Delete Shift:=xlUp
End If
End Sub

Answer #2    Answered By: Eloise Lawrence     Answered On: Mar 01

Thank you very much , it works like a charm......

Answer #3    Answered By: Doyle Gonzalez     Answered On: Mar 01

You may use following code:

Dim TotalRows As Integer
Dim Counter as Integer
For Counter=1 to TotalRows
If Range("D"&Counter).Value=25 OR Range("D"&Counter).Value=15 OR
Range("D"&Counter).Value=16 Then
End If

Answer #4    Answered By: Balbir Kaur     Answered On: Mar 01

when i run the code , i get a message saying " run time  error '6' Overflow

Answer #5    Answered By: Rene Sullivan     Answered On: Mar 01

The code worked for me.

Review your code, line wrapping in email messages can cause problems
with code.

This should wrap properly for you. I just made the lines of code
shorter without adjusting any elements of the code.

Sub DeleteCertainRows()
Dim TotalRows As Integer
Dim Counter As Integer
TotalRows = Range("D65536").End(xlUp).row
For Counter = 1 To TotalRows
If Range("D" & Counter).Value = 25 Or _
Range("D" & Counter).Value = 15 _
Or Range("D" & Counter).Value = 16 Then
End If
ActiveCell.Offset(1, 0).Select

End Sub

Didn't find what you were looking for? Find more on select cells based on 3 criteria and delete rows Or get search suggestion and latest updates.