copy from a list

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

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?
 
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.
 
Back
Top