Need help with modify this macro to copy.

[Guest]

This macro show the last cell in a sheet that has an empty row. I want it to
copy all rows from A16 till the last row in the sheet that is not empty or
looks empty.
I can't use .End(xlUp) since that copies empy cells with formulas in them.

Sub testme()

Dim myCell As Range
Dim NextEmpty As Range

Set myCell = ActiveSheet.Range("a16")
Do
If myCell.Value = "" Then
Set NextEmpty = myCell
Exit Do
Else
Set myCell = myCell.Offset(1, 0)
End If
Loop

MsgBox NextEmpty.Address

End Sub

 

[Tom Ogilvy]

Sub testme()

Dim myCell As Range
Dim NextEmpty As Range

Set myCell = ActiveSheet.Range("a16")
Do
If myCell.Value = "" Then
Set NextEmpty = myCell
Exit Do
Else
Set myCell = myCell.Offset(1, 0)
End If
Loop
Range("A16", NextEmpty.offset(-1,0).EntireRow.copy _
Destination:=Worksheets(2).Range("A1")
End Sub

 

[Guest]

Hi Tom!
I get an error message with these to lines:

Range("A16", NextEmpty.offset(-1,0).EntireRow.copy _
Destination:=Worksheets(2).Range("A1")

 

[Tom Ogilvy]

since that was too hard to debug and correct yourself, I will post the
tested routine

Sub testme()

Dim myCell As Range
Dim NextEmpty As Range

Set myCell = ActiveSheet.Range("a16")
Do
If myCell.Value = "" Then
Set NextEmpty = myCell
Exit Do
Else
Set myCell = myCell.Offset(1, 0)
End If
Loop
Range("A16", NextEmpty.Offset(-1, 0)).EntireRow.Copy _
Destination:=Worksheets(2).Range("A1")
End Sub