View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
Terry Pinnell[_4_] Terry Pinnell[_4_] is offline
external usenet poster
 
Posts: 192
Default Go to a column in a selected row?

GS wrote:

I have a full row selected and I've opened the Go To dialog. The text
cursor is at its left end. What do I now type in the Reference field
to select the cell in (say) column H please?

https://dl.dropboxusercontent.com/u/...cel-GoTo-1.jpg

Alternatively, how can I get the row number rrr copied to the
clipboard, so that I could then enter the specific cell address Hrrr?


There's a couple of ways you could go:

Give colH a Defined Name as follows:

Select A1 and open the define name dialog;
Enter the same name as the header (minus illegal naming chars);
Make it sheet level;
In the RefersTo box type: =$H1
(..make it col-absolute, row-relative)

Now you can select the name in the NameBox instead of using
GoTo dialog when any cell in the row is selected!


Use a macro:

Sub GoToH()
Application.GoTo {"H" & ActiveCell.Row]
-OR-
Application.GoTo {NamedCol] '//my preference
End Sub

You could assign a keyboard combo if you use it a lot.


Thanks Garry, I'll try both of those approaches.

I realised after posting that my query might sound a bit strange. I
should have reminded others of the following background.

I write macros with a program called Macro Express Pro, using hundreds
of them to improve PC productivity in and between many applications. The
macro prompting my post is intended to automatically copy about 40
values from cells in a worksheet SingleWalk.xlsm into a worksheet
WalkIndex.xlsm, which is the sheet in my question. (There are hundreds
of sheets to be copied into WalkIndex.)

At the moment I have to include user interaction within my macro, asking
for *manual* entry of the current row number.

--
Terry, East Grinstead, UK