Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 11th 17, 06:42 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 114
Default Go to a column in a selected row?

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?

--
Terry, East Grinstead, UK


  #2   Report Post  
Old February 12th 17, 06:28 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 779
Default Go to a column in a selected row?

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.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #3   Report Post  
Old February 12th 17, 01:40 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 114
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
  #4   Report Post  
Old February 12th 17, 11:46 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 779
Default Go to a column in a selected row?

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.


Yes, I recall that you use ME and so did check it out!

Here you'll only get Excel-based solutions so we don't need to know ME
is being used at your end.

I prefer a cleaner (and easily distributable) approach...

store the source data files in 1 folder and use ADODB from my Excel
project file to read all the sought data in without having to open
the source files!

...so others do not require the external dependency (ME) to do the task.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #5   Report Post  
Old February 13th 17, 11:41 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 114
Default Go to a column in a selected row?

GS wrote:

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.


Yes, I recall that you use ME and so did check it out!

Here you'll only get Excel-based solutions so we don't need to know ME
is being used at your end.

I prefer a cleaner (and easily distributable) approach...

store the source data files in 1 folder and use ADODB from my Excel
project file to read all the sought data in without having to open
the source files!

..so others do not require the external dependency (ME) to do the task.


I too would far prefer a VBA-only solution, but my skills are not up to
it!

Expanding on my earlier description:
https://dl.dropboxusercontent.com/u/...el-Copy-02.jpg

I've written and tested the macro for the first two of the 40 cells, but
it's clear that it's a tedious exercise, And I expect quite slow to run,
even on one worksheet X.

Could you amplify on "...use ADODB from my Excel project file" please?
Is this something I can look at somewhere?

--
Terry, East Grinstead, UK


  #6   Report Post  
Old February 13th 17, 08:30 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 779
Default Go to a column in a selected row?

Could you amplify on "...use ADODB from my Excel project file"
please?
Is this something I can look at somewhere?


Have a look at these examples...

http://www.appspro.com/conference/Da...rogramming.zip

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #7   Report Post  
Old February 14th 17, 10:08 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 114
Default Go to a column in a selected row?

GS wrote:

Could you amplify on "...use ADODB from my Excel project file"
please?
Is this something I can look at somewhere?


Have a look at these examples...

http://www.appspro.com/conference/Da...rogramming.zip


Presumably after reading 'DatabaseProgramming.doc', which I assume is
what you intended to be the 'explanatory introduction'?

Too heavy for me thanks.

--
Terry, East Grinstead, UK

  #8   Report Post  
Old February 14th 17, 10:23 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 779
Default Go to a column in a selected row?

GS wrote:

Could you amplify on "...use ADODB from my Excel project file"
please?
Is this something I can look at somewhere?


Have a look at these examples...

http://www.appspro.com/conference/Da...rogramming.zip


Presumably after reading 'DatabaseProgramming.doc', which I assume is
what you intended to be the 'explanatory introduction'?

Too heavy for me thanks.


Well.., given the number of years I've seen you posting I thought it
would give you a good 'kick-start' since it's a very basic intro to
using ADODB. It certainly would be orders of magnitude faster
processing of all the files, not to mention much more flexible in terms
of stepping through any random set of filenames!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Protect selected column Fareez Excel Discussion (Misc queries) 1 February 20th 09 07:50 AM
HOW DO I ALPHABETIZ A SELECTED COLUMN Ann Longley Excel Worksheet Functions 1 May 22nd 06 05:16 PM
Last row - Selected Column Desert Piranha[_2_] Excel Programming 6 November 23rd 05 08:12 AM
Don't run if entire column is selected Elaine Excel Programming 4 March 21st 05 03:01 AM
Is column selected Josh Sale Excel Programming 3 August 10th 04 02:47 PM


All times are GMT +1. The time now is 03:36 AM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017