SendKeys Down, Not Working

[emacjake]

Hi

I'm writing a macro that copy's and pastes values in filtered data

I want to copy the price of something to an adjacent column as a value, which is easy in the first column, but i'm trying to then select the next visible cell below and do the same
with :

SendKeys ("{DOWN}")

but nothing happens when stepping through the macro it goes passed this step and no change to the active sheet?


Any ideas how i can get this to work? without looping through [FONT=&quot]EntireRow.Hidden = False, as the sheet is 5000 rows long

[/FONT]Cheers

 

[Jeffrey Mahoney]

That is not a preferred method of selecting the next cell. I rarely have ever used the SendKeys command. Here's a SUB you can use. Just call it from whithin your macro. The Test sub explains how to call the sub. Rng is returned and used to either select the next visible cell or just use it as a reference.

Sub SelectNextVisibleCell(Rng As Range)
  Dim Cel As Range
  For Each Cel In Range(Rng.Offset(1, 0), Rng.Offset(1000, 0))
    If Cel.EntireRow.Hidden = False Then
      Set Rng = Cel
      Exit Sub
    End If
  Next Cel
End Sub


Sub TestNextCel()
  Dim Rng As Range
  Set Rng = ActiveCell
  Call SelectNextVisibleCell(Rng)
  Rng.Select
End Sub