vba delete entire row based on input value

[abhinav023]

Hi

I'm trying to write a code so that excel would go through each cell in a range and if the cell value is equal to "inputbox entered value" it will write ok and delete everything else.

The problem is that if i'm assigning any other value in adjacent column or changing font types etc. then it works fine. but if i use the delete row option, it skips a few item. Please find below the code i wrote:-

Sub LoopA()
Dim cell As Range
Dim Portionnumber As Integer
Dim Msg As String

Portionnumber = InputBox("Enter the portion to be billed")

If Portionnumber <> 1001 And Portionnumber <> 1002 And Portionnumber <> 1003 And Portionnumber <> 1004 Then
Msg = "Enter a valid Portion number i.e. 1001/1002/1003/1004"
MsgBox Msg
Exit Sub
End If

For Each cell In Range("A:A")
If IsEmpty(cell) Then
Exit Sub

ElseIf cell.Value = "Portion" Then
cell.Offset(0, 1).Activate
ActiveCell.Value = "Type"
ElseIf cell.Value = Portionnumber Then
cell.Offset(0, 1).Activate
ActiveCell.Value = "OK"
Else: cell.EntireRow.Delete

End If
Next cell

End Sub

 

[Joe4]

Welcome to the Board!

Whenever deleting entire rows, you want to loop through your range backwards, otherwise you will miss rows (as your loop moves down and your data moves up).

I would find your last row with data like this:

Dim myLastRow as Long
myLastRow = Cells(Rows.Count,"A").End(xlUp).Row

Then loop through all your cells in column A like this:
Dim myRow as Long

For myRow = myLastRow to 1 Step -1
    ...
Next myRow

You would reference column A within your loop like this:

Cells(myRow,"A")

likewise, column B would be:

Cells(myRow,"B")

Try re-writing your code with that in mind, and see if you can get it to work.