trying to loop range but getting error

[bintaleb]

Hi Friends

I have used for next to loop through range but I got subscript out of range

here is my code.

 Dim i As Long
    For i = 1 To Sheets("Sheet2").Range(A1, S1000).Rows
        If Sheets("Sheet1").Range("F5") = Cells(1, "R") Then
         Selection.Rows(i).EntireRow.Delete
        End If
    Next i
    Range("F22") = i

can any one help me what is the problem?

thanks

[IamLoki]

I'm no expert but Cells(1,"R") must be an error as the format is Cells(Row,Column) and I presume you want the value, i.e. Cells(Row,Column).Value.

[westconn1]

Cells(1,"R") must be an error as the format is Cells(Row,Column)

it will accept a string or the column letter

If Sheets("Sheet1").Range("F5") = Cells(1, "R") Then

surely that should be cells(i, "R")

which line gives the error at what value of i

as the code is it should delete no rows or every row
remember when you delete rows from a loop the row index changes, so you will skip the row after the deleted row

[vbonline]

Use a variable and store last column and LastRow
then use two loop

Last Columns = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column

LastRow = Cells(Cells.Rows.Count,"A").End(xlUp).Row