Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect selected column | Excel Discussion (Misc queries) | |||
HOW DO I ALPHABETIZ A SELECTED COLUMN | Excel Worksheet Functions | |||
Last row - Selected Column | Excel Programming | |||
Don't run if entire column is selected | Excel Programming | |||
Is column selected | Excel Programming |