trying to loop range but getting error
Hi Friends
I have used for next to loop through range but I got subscript out of range
here is my code.
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
Re: trying to loop range but getting error
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.
Re: trying to loop range but getting error
Quote:
Cells(1,"R") must be an error as the format is Cells(Row,Column)
it will accept a string or the column letter
Quote:
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
Re: trying to loop range but getting error
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