Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with modify this macro to copy.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with modify this macro to copy.
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 -- Regards, Tom Ogilvy "Calle" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with modify this macro to copy.
Hi Tom!
I get an error message with these to lines: Range("A16", NextEmpty.offset(-1,0).EntireRow.copy _ Destination:=Worksheets(2).Range("A1") |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with modify this macro to copy.
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 -- regards, Tom Ogilvy "Calle" wrote in message ... Hi Tom! I get an error message with these to lines: Range("A16", NextEmpty.offset(-1,0).EntireRow.copy _ Destination:=Worksheets(2).Range("A1") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need to modify my macro ... | Excel Discussion (Misc queries) | |||
How do I copy a graph that I want to modify in powerpoint? | Charts and Charting in Excel | |||
Modify macro to copy to next available row | Excel Discussion (Misc queries) | |||
How to modify a copy macro | Excel Worksheet Functions | |||
Help to modify macro please | Excel Programming |