Send Keys

[Akshay Bakhai]

I have a large list of data; on which I have applied
automatic filtering.

Thereafter, I use filter conditions on one of the columns
only to show selected records.

Thereafter I want to automatically run down this column
and do specific actions for each value in this column. I
am able to do everything I want EXCEPT run down this
column.

The ACTIVECELL.OFFSET(1,0) obviously gives the physically
next record; and so is not useful.

I tried SENDKEYS "{DOWN}" to simulate pressing the down
key; but for some strange reason it takes me to the last
item in that column !!

I tried SENDKEYS "{ENTER}" to simulate pressing the ENTER
key. But nothing happens.

Thus, currently I have to manually press enter every time
before triggering off my macro.

Any help will be useful.

I do not want to write a FIND routine because eventually
in my filter I may set filter conditions on several fields
and then run down a column; in which case my FIND routines
may not be that reliable or scalable.

Many thanks

 

[Otto Moehrbach]

Range("A1").End(xlDown) does the same thing as pressing the End key and then
the down arrow key. HTH Otto

 

[steve]

Akshay,

The following code works on the visible cells in A1:A11

Dim cel As Range
For Each cel In Range("A1:A11").SpecialCells(xlCellTypeVisible)
Cells(13, cel.Row) = cel
Next

see if you can apply it to your needs.

 

[steve]

Pancho,

When I copy the current region and paste, I get everything. Even the
hidden cells. That is why I select only the visible cells.

 

[pancho]

maybe is an Excel 2000+ feature, because, when I copy the
current region of a filetered data then I paste only the
visible data

Francisco Mariscal
fcomariscal at hotmail dot com

 

[steve]

Pancho,

Could be. I'm still in the "dark ages" with 97 (can't update until all my
users update - what a ?@#@#!)

 

[pfsardella]

And another method.

For Each rngF In rngFilter
If rngF.EntireRow.Hidden = False Then
'' Do your thing.
End If
Next rngF

HTH
Paul

 

[Akshay Bakhai]

Thanks steve, this does the trick neatly.

Thanks to all others also, your suggestions also worked
fine.

Many thanks.

 

[steve]

Akshay,

You're very welcome!

--
sb

Thanks steve, this does the trick neatly.

Thanks to all others also, your suggestions also worked
fine.

Many thanks.