Select cell with Ctrl+G to a specfic column

[terrypin]

If I am in col X of any row, is there some Reference I can enter in the Go To dialog that will select the cell in the current row but in column B? I want to do it programmatically, not manually, for use in an external script.

Coming at it from a different direction, is there some direct way (apart from VBA) to get the Current Row onto the clipboard (or even just into a specific fixed cell), so that I could then enter 'BRow' as the Go To reference?

[GlennUK]

What do you mean by "external script"? Programmatically hints at VBA ... in which case you wouldn't use the Go To dialog ... so you request makes no sense to me.

[MarvinP]

Hi terry,

Here is the VBA code that will do what you want.

Please Login or Register  to view this content.

Assign this macro to a key combination or put it on the QAT (or both)

[terrypin]

Thanks Marvin. I was hoping to do it without any use of VBA, using only a macro (essentially simulating mouse and key actions) written with Macro Express Pro. But I gave in last night and adopted your solution. I used VBA code I already had, almost identical to yours:

Please Login or Register  to view this content.

(I'll change "z1" to your more intuitive "z".)

I'll also need several further variations, i.e. selecting columns other than B or Z, the two implemented so far. Your approach gives me the basis for that general solution, thank you.

Adding them to the QAT was not an idea I'd considered for this particular case, because I will be using these small selection tasks as only a small part of MX Pro macros. I've developed a reliable method of triggering such VBA macros from MX Pro: basically telling Excel to run PERSONAL.xlsb!GoToColZ for example.

[terrypin]

Glenn: I'm using a program called Macro Express Pro (MX Pro). It creates 'macros'. To avoid potential ambiguity with Excel VBA macros I used the alternative term 'script'. That's what its macros consist of, written in its Script Editor.

[MarvinP]

Hi terry,

I've used Windows Macro programs in the past because I was dealing with multiple and different programs. In Excel you can use "SendKeys" that puts keystrokes in the keyboard buffer, just like typing them in. The problem with SendKeys and Macro programs is timing, and other programs in the way. If you put keystrokes into the buffer before it is ready, as in not in the correct program, things go south very fast. Excel also has DoEvents which will wait for the OS to do other things before continuing.

[terrypin]

Thanks Marvin.

I haven't yet used either SendKeys or DoEvents in my limited VBA excursions, so I'll read up on them and try some simple examples soon.

Yes, timing is indeed a pain. It's usually the most likely cause of one of my MX Pro macros failing - usually intermittently, of course!

One significant advantage that MX Pro has for me (apart from the greater ease of use of its GUI for a non-programmer when creating macros) is the far wider range of activation options. For those that I choose to activate with KB or mouse shortcuts there are thousands of possible combinations, compared with the small 'Ctrl + Shift' repertoire in Excel.