Loop with LastRow not working

[Questionboy]

I've searched and searched for an answer to this and can't find a solution. I have written a code that compares Cells on 1 row to the Cells on the row above, if they match they get deleted. I've then looped it to run through each row and then repeat the whole process 15 times. I have got it to work when i declare the last row but if i try and use a dynamic last row the code fails. can someone point out my error.

The code below is what i'm trying to get working and it fails. with a comment as to how i can get it to work.

Sub test()
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row


For NN = 1 To 15 Step 1
For N = LastRow To 2 Step -1 'If i change this row to For N = 100 to 2 Step -1 the code does what i want
If Cells(N, 5) = Cells(N - 1, 5) And Cells(N, 6) = Cells(N - 1, 6) Then
Range(Cells(N, 5), Cells(N, 6)).Delete Shift:=xlToLeft
End If
Next N
Next NN


End Sub

obviously it will work with me work around but i cannot guarantee i'll never have more than 100 rows of data

thank you for the advice.

 

[steve the fish]

Firstly what is the NN loop doing? Nothing i can see. Then under the LastRow = Cells etc line place this:

Msgbox LastRow

What do you get? I suspect 1.

 

[steve the fish]

Try it with this:

LastRow = Cells.Find(What:="*", Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

 

[Questionboy]

Thank you both for replying! I have just spent the last hour trying to work out what happen. as i have opened the file today to continue working on it and my original code is working as i wanted it too!

The only thing i did was save the file and re-open it again.

 

[Questionboy]

I tried the Msgbox LastRow yesterday and i was getting 0, today i'm getting 18 which is what i was expecting. no idea what was going on yesterday.

The NN Loop is simply repeating the code 15 times. The reason for it is i'm using it to tidy data. I have data where columns A to D are project details then Columns E to Z are names of partners on the project and their organisations in pairs of columns. Iwanted to separate each partner and organisation on to different rows but keep A:D the same. I did this by repeating the project row by the number of partners. Then this code takes each row and compares and deletes the partners the result is i get a row per partner. the next line of code i didn't include deletes columns G:Z as this no holds redundant data. There may be a simpler way but this does the job for me.

 

[steve the fish]

The one i posted could never produce 0 far as i can tell? Anyway if you are getting different answers its because you run the code with different active sheets. If you need it to look at a particular sheet you need to include the sheet within the code eg.

LastRow = Sheets("Sheet1").Cells.Find(What:="*", Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row