IsEmpty

Hello Determined,

You were really close...

Worksheets("Sheet1").Range("Input1").Select
r = ActiveCell.Row
lr = ActiveSheet.UsedRange.Cells(Rows.Count, "A").End(xlUP).Row
Rows(r & ":" & lr).Select
Selection.Copy

There is a possible problem. In this code I used column "A" to find the last row. Each column may not have the same number of non blank rows. You will need to select the column that will always have the most rows filled.

Sincerely,
Leith Ross

This is still capturing all rows that have data on my spreadsheet even though there is an empty row between the sets of data.

Hello Determined,

Sorry, I misunderstood your post. Will you be looking for an entire empty row or just an empty cell in a specific column? Let me know, I'll rewrite the macro.

Sincerely,
Leith Ross

I am looking for an entire empty row. Thank you for your help.

Hello Determined,

This macro will select the ActiveCell to the last column in the UsedRange down to the row above the first empty row found. Checks are made to ensure the ActiveCell is not in a blank row or outside the UsedRange (Data area).

Sub SelectRows()
  
  Dim SearchRng As Range
  
   StartRow = ActiveCell.Row
   StartCol = ActiveCell.Column
   ColCount = ActiveSheet.UsedRange.Columns.Count
   FirstCol = ActiveSheet.UsedRange.Column
   
     If Intersect(ActiveCell, ActiveSheet.UsedRange) Is Nothing Then
        MsgBox "Your selection is outside the data."
        Exit Sub
     End If
     
     With ActiveSheet
       Set SearchRng = .Range(Cells(StartRow, FirstCol), Cells(.UsedRange.Rows.Count, ColCount))
     End With
      
     For Each CurRow In SearchRng.Rows
       Set TestRow = CurRow.SpecialCells(xlCellTypeBlanks)
         If TestRow.Columns.Count = ColCount Then
            If Not Intersect(ActiveCell, TestRow) Is Nothing Then
               MsgBox "You have selected an empty row."
               Exit Sub
            End If
            LastRow = CurRow.Row - 1
            ActiveSheet.Range(Cells(StartRow, StartCol), Cells(LastRow, ColCount)).Select
            Exit Sub
         End If
     Next CurRow
       
End Sub

Sincerely,
Leith Ross