copy from a list
 

I wrote the following macro to copy the contents of the
cell above the cursor into the cursor's current location.

ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste

It works fine until I filter the list. For example, when
I fliter, the cursor might be on A12 and the next row up
is A10. When I run my macro, it returns the value that's
in A11. (A11 isn't displayed since it was filtered out).

What should the macro be?

copy from a list
 

Sub AAAcopydown()
Dim cell As Range
Set cell = ActiveCell.Offset(-1, 0)
Do While cell.EntireRow.Hidden = True And cell.Row 1
Set cell = cell.Offset(-1, 0)
Loop
If cell.EntireRow.Hidden = False Then
ActiveCell.Value = cell.Value
End If
End Sub

would be a start.

--
Regards,
Tom Ogilvy




"Bob" wrote in message
...
I wrote the following macro to copy the contents of the
cell above the cursor into the cursor's current location.

ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste

It works fine until I filter the list. For example, when
I fliter, the cursor might be on A12 and the next row up
is A10. When I run my macro, it returns the value that's
in A11. (A11 isn't displayed since it was filtered out).

What should the macro be?