Delete Rows in Column A with values 5

[JEHalm]

Hi everyone, another afternoon trying to work a little sub routine. I have 12,000 rows with values from 1 to 11. I would like to sort column A and delete all rows <> "5" (the vlue of 5). Below is a sub I copied out of Mr. Excel but I was unable to get it to work. The sort was not included as seen below. Where am I going awry?


____________________________________________________________________________

Sub test()

'start at row 1

Dim i As Long

i = 1


'while cell C contains something

While Cells(i, 1) <> ""

'select the row

Rows(i).Select 'this is optional

' if D >= C

'If Cells(i, 4) >= Cells(i, 3) Then

'delete the row

Rows(i).Delete

Else 'or

'go to the next row

i = i + 1

End If

Wend


End Sub

 

[Hui]

JEHalm


In the title of your post you say value=5 and in the body of the post you ask rows <> "5"


So I have assumed you want to delete the rows = 5

if you want the rows <> 5 change the =5 <>5

[pre]

Sub Delete_Rows()
Dim x As Integer
For x = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Rows.Count To 1 Step -1
If Cells(x, 1).Value = 5 Then Cells(x, 1).EntireRow.Delete Shift:=xlUp
Next x
End Sub

[/pre]

When deleteing rows I always recomend starting at the bottom and working up.

 

[asa]

The reason by-the-way for deleting rows from the bottom up is that when you delete a row, your data has all moved up a row, and your loop will end up skipping/not checking some rows of data, and then checking a bunch of blank rows at the bottom of your table, if you use a regular FOR loop or FOR EACH loop to traverse your range. Traversing the rows in reverse is the simplest solution.

 

[vijaySharma]

If you need to delete the entire row for the cells having the value of 5 below is the code for the same.

[pre]

Sub DeleteRows()

Range("A1").Select
Do While ActiveCell.Value <> ""
If ActiveCell.Value = 5 Then
ActiveCell.EntireRow.Delete
ActiveCell.Offset(-1, 0).Select
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub

[/pre]

~VijaySharma

 

[mkapashi]

This will work really fast (I timed it - 0.6 seconds). This presumes that there is a header row. If not then create one. Also that your data starts from row 2 (header being row 1).


Sub DeleteRows()

Application.ScreenUpdating = False

Dim r As Long

r = [a1].CurrentRegion.Rows.Count

Range([a1], Cells(r, 1)).AutoFilter Field:=1, Criteria1:="<>5"

Range([a2], Cells(r, 1)).SpecialCells(xlCellTypeVisible).EntireRow.Delete

r = [a1].CurrentRegion.Rows.Count

Range([a1], Cells(r, 1)).AutoFilter

End Sub