Delete rows unless meets a criteria

B

Btibert

Hi everyone,

I was hoping you guys could assist me with a simple task.

I was hoping that starting at a given cell, I could delete every ro
that did not meet a criteria that was determined by the value in colum
"b". Simply put, my output is quite large, and want to start from
specific row, and delete every row that does not equal one of tw
values, Vert% or Proj?

How can write the vba to do this. Ive been playing around with
number of things and cant get anything to work properly?

Any assistance will be greatly appreciated as usual.

Thanks

Broc
 
F

Frank Kabel

Hi
one way to delete them:
Sub delete_rows()
Dim lastrow As Long
dim firstrow as long
Dim row_index As Long

'change the following line according to your requirements
firstrow=20

Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
For row_index = lastrow To 1 Step -1
If Cells(row_index, "B").Value <>"Proj" and _
Cells(row_index, "B").Value <>"Vert%" then
rows(row_index).delete
End If
Next
Application.ScreenUpdating = True
End Sub
 
B

Bob Umlas

You need to do it backwards - from the last row to the desired row. This
procedure assumes you want to go from the selected row to the end:

Sub DeleteSpecial()
LastRow = Range("B65536").end(xlup).row
For i= LastRow to Selection.Row step -1
If cells(i,2).Value <> "Vert%" and cells(i,2).Value <> "Proj" then
rows(i).Delete
Next
End Sub

If this runs slowly, you can put Application.Calculation =
xlcalculationmanual at the beginning, and Application.Calculation =
xlcalculationAutomatic at the end.
Bob Umlas
Excel MVP
 
B

Btibert

Thank you so much, that worked great!!

One quick question, how can I modify it such that it will not delet
any information above the desired row?

Thank you so much,

Broc
 
B

Btibert

Thanks for the help everyone. I did some tweaking on my own and it
works as desired.

I appreciate your help and willingness to assistist novice/intermediate
level programmers like myself.

Thanks again


Brock
 
Top