VBA loop trough until cell is blank

[DMO123]

Hi All, i have the below code at the moment it just runs on cell Z1 but i want it to move down column Z fetching the data until the cells are blank so the code would run complete Z1 then loop round dropping to Z2 etc. how do i add the loop in?

Option Explicit

Sub Find_Data()


Dim casenumber As String
Dim finalrow As Integer
Dim i As Integer


Sheets("Data").Range("AD2:AV400").ClearContents


casenumber = Sheets("Data").Range("Z1").Value
finalrow = Sheets("Data").Range("A10000").End(xlUp).Row


For i = 2 To finalrow
    If Cells(i, 1) = casenumber Then
        Range(Cells(i, 1), Cells(i, 19)).Copy
        Range("AD1000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
        End If


Next i


Range("Z:Z").Select


    casenumber = casenumber + 1
Loop


End Sub

 

[Fluff]

How about

Sub Find_Data()
   Dim CaseNumber As Range
   Dim FinalRow As Long, i As Long
   Dim Mch As Variant
   
   With Sheets("Data")
      .Range("AD2:AV400").ClearContents
      Set CaseNumber = .Range("Z1", .Range("Z" & Rows.Count).End(xlUp))
      FinalRow = .Range("A" & Rows.Count).End(xlUp).Row
      For i = 2 To FinalRow
         Mch = Application.Match(.Cells(i, 1).Value, CaseNumber, 0)
         If Not IsError(Mch) Then
            .Cells(i, 1).Resize(, 19).Copy
            .Range("AD" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
         End If
      Next i
   End With
End Sub

 

[DMO123]

works perfectly - thank you!

 

[Fluff]

You're welcome & thanks for the feedback