Hi Mike,

Thank you for additional help.

When I run Sub test(), I get run-time error '1004' on this line: If IsEmpty
(Range("A" & i)) Then

error message Method 'range' of object '_Global' failed.

I have also modified the code to run on each sheet in the workbook, as you requested of
Tom.

Could the above be specific sheets I list in an array, rather than all sheets?


Please advise.

Very much appreciated.

Cheers,
Sam

Mike Fogleman wrote:
Sam, this will work to some extent if there is data below the target range,
it is also contiguous, and there is only one section of data. I have also
modified the code to run on each sheet in the workbook, as you requested of
Tom.
If there are several areas of data below the target data, then we will need
a way to differentiate among them to find the range.

Sub test()
Dim LRow As Long
Dim MyRng As Range
Dim i As Long
Dim ws As Worksheet

For Each ws In Worksheets
ws.Activate
LRow = Cells(Rows.Count, 1).End(xlUp).Row
Set MyRng = Range("A" & LRow)
i = MyRng.CurrentRegion.Rows.Count
i = LRow - i
If IsEmpty(Range("A" & i)) Then "ERROR returned"
i = i - 1
If i = 0 Then i = 1
Set MyRng = Range("A" & i)
Do While IsEmpty(MyRng)
i = i - 1
If i = 0 Then i = 1
Set MyRng = Range("A" & i)
If MyRng.Row = 1 Then
LRow = Cells(Rows.Count, 1).End(xlUp).Row
Set MyRng = Range("A" & LRow)
Exit Do
End If
Loop
LRow = MyRng.Row
Range(MyRng, MyRng.End(xlToRight)).Copy Range("A" & LRow + 1)
End If
Next
Worksheets(1).Activate
End Sub

Mike F

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200709/1