How to set programmatically focus on specified Excel row?

[Jack]

Hello,
1.
How to programmatically move focus from one Excel row into next one?
2.
How to programmatically make cell an ActiveCell?

Your thoughts appreciated,
Jack

 

[Dave Peterson]

Just select a different cell or row.

#1. rows(17).select
#2. range("b92").select

 

[Jack]

I forgot to add.
When I try:
oExcel.Rows(CurrentRow, CurrentCol).Activate
I do have an error.

 

[Dave Peterson]

oExcel.cells(CurrentRow, CurrentCol).Activate

????

 

[Jack]

Thank you.
Using .Select works.
However I am "loosing" the ActiveCell, when using Rows().Select
Before selecting the row I had specified ActiveCell.
When Rows().select is done the first cell in that row becomes active. I do
not want that.
Jack

 

[Gman]

I'm not quite sure what you're trying to do -- or more accurately why...

Whenever you select a row AFAIK it *always* selects the first cell in
the row. Thus, to achieve what you seem to be asking you would need to
revert to the originally selected cell. E.g.

Sub SelectRowOfActiveCellRetainingActiveCell()
Dim r As Range
Set r = ActiveCell
Rows(r.Row).Select
r.Activate
Set r = Nothing
End Sub

Now, if you subsequently want to drop down a row try this

Sub SelectNextRowOfActiveCellIfYouKnowWhatIMean()
Dim r As Range
Set r = ActiveCell.Offset(1, 0)
Rows(r.Row).Select
r.Activate
Set r = Nothing
End Sub

My recommendation: Explain why you're trying to do all this and maybe
someone will come up with a better approach.

HTH

 

[NickHK]

Jack,
You said you wanted "to programmatically make cell an ActiveCell".
So the previous ActiveCell will no longer be active.

It is normally not necessary to .Select object in VBA to use them. Do you
really need to move the ActiveCell/selection ?

NickHK

Thank you.
Using .Select works.
However I am "loosing" the ActiveCell, when using Rows().Select
Before selecting the row I had specified ActiveCell.
When Rows().select is done the first cell in that row becomes active. I do
not want that.
Jack

 

[keepITcool]

when you want to retain the activecell
and its not on the same row, you need a multiarea select
like:

Union(ActiveCell, Rows(13)).Select

dont use rows(currentrow

use Cells(currentRow,desiredCol).Select
or
activecell.entirerow.cells(desiredCol).select
or
activecell.entirerow.columns(desiredCol).select
or again to retain the activecell

union(activecell,cells(activecell.row,desiredCol)).select

many roads to Rome


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Jack wrote :