Selective Copy Data from Sheet A to Sheet B without Blank Lines

[scooter133]

I would like to take Data from the "ALL REPS" Sheet and selective Copy out Data from it to another Sheet.

I have the Below code that seems to work, though it has some issue. When it gets to line 10 in 'ARC 5A' it just copies over the same row over and over.
If I remove the Clear Content code, and rerun the Code. I get the same rows appended to the bottom, where the last row get over written. I've tried a vew variants, and all are the same.

Why is my last row getting over written after so many rows?

Thanks!

Sub Summary()


'Clear Current Contents
Sheets("ARC 5A").Range("A3:AZ65536").Select
Selection.Clear

' Range for the Rows
Dim r As Integer
For r = 2 To 20000
    'This will check the first 20000 rows of the sheet
    '"ALL REPS" Contains All the Data "ARC 5A" Contains Subset of "ALL REPS"

    'ARC 5A  B1 has Search Criteria, "ALL REPS" Column AA Has Field with Data
    If Sheets("ALL REPS").Range("AA" & r).Value = Sheets("ARC 5A").Range("B1").Value Then
      ' Found a Row, Copy it
      Sheets("ALL REPS").Rows(r & ":" & r).Copy

      ' Paste the Row at the End of our "ARC 5A" Sheet
      'Sheets("ARC 5A").Range("A65536").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
      'Sheets("ARC 5A").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll
      Sheets("ARC 5A").Range("A1").End(xlDown).Offset(1, 0).PasteSpecial xlPasteAll

   End If
Next r
End Sub

 

[scooter133]

I found out why it kept overwriting the Rows, though not sure how to fix it. Column A has some data in it, though not all Rows do. So when it gets to a row with no data, it starts to overwrite that row. I tried changing to Column D which has Data, though I got Errors.

How can I find the Last ROW with Data in any Column vs a Specific Column?

Thanks!