VBA For loop + if statement error

Question

I am trying to create a loop that finds the value of my input box, select the cell that has the matching value of my input box and delete its the entire row. For some reason It seems like my if condition is not working at all.

*Totalrow= my double variable for the total number of rows on my sheet *tenroxcode = my string variable for the inputbox

For i = 1 To totalrows
    If tenroxcode = Range("E" & i).Value Then
        Range("E" & i).Select
        ActiveCell.EntireRow.Delete
    End If
Next

Could someone PLEASE help me?

cybernetic.nomad · Accepted Answer · 2018-07-30 20:24:18Z

2

You need to loop backwards:

For i = totalrows to 1 step -1
    If tenroxcode = Range("E" & i).Value Then Rows(i).Delete
Next

answered Jul 30, 2018 at 20:24

cybernetic.nomad

6,5763 gold badges20 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user4039065 · Accepted Answer · 2018-07-30 20:36:17Z

0

Alternate that should be significantly faster.

dim r as variant

r = application.match(tenroxcode, columns("E"), 0)
do while not iserror(r)
    rows(r).entirerow.delete
    r = application.match(tenroxcode, columns("E"), 0)
loop

answered Jul 30, 2018 at 20:36

user4039065

Collectives™ on Stack Overflow

VBA For loop + if statement error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related