The Old Joel on Software Forum: Part 4 (of 5)

Excel VBA question

Not sure what the name of the feature is so it's hard to search for help on it, but...
There is a feature in Excel which allows you to select a range of rows and/or columns, and Excel automatically fills in the cell coordinates. For example, when setting 'Rows to Repeat at Top' in the 'Page Setup' dialog, you can manually enter you range of rows (e.g. '1:3'), OR you can click that spiffy little red button to the right, which hides the Dialog and allows you to use the mouse to select a range of rows on your spreadsheet. After you make your selection and hit 'Enter', it brings you back to the page setup dialog, but this time with the range you selected filled in (e.g. '$1:$3').
My question is... Can I utilize this feature in a macro so that I can have the user select a range of rows/cells, and then have the range that they selected returned to my macro so that I can use it in an arbitrary fashion?
[In case you're interested, I am trying to automate the import of Excel spreadsheets into an Access database, and it would be nice if the user could just select with the mouse the rows/columns they want to import instead of having to type in the row numbers/column letters themselves. And please don't tell me to use Access's Import... function -- this is a very customized import process in which the users don't have acces to (or an understanding of) the table structure.]
Thanks for any help.

Jordan Lev
Tuesday, February 10, 2004

For the most part the answer is yes.

A really easy way to do this sort of thing, is to use the 'record macro' function (under the tools menu, which I am sure you know).

Once you have record the macro, open the VBA editor (alt-F11) and check out the code.
I find it is usually pretty messy (a bit like getting Dreamweaver to create your webpage), but you can play with it, tidy it up etc.

If you are having problems, email me. Coding in VBA is about the only coding I get to do….(okay well things aren’t *that* bad).

Aussie Chick
Tuesday, February 10, 2004

I don't work with Excell all that much, but do you need to do that in the middle of the macro, or can you have them select it, then invoke the macro, and tell the macro to simply use the currently selected range?

Sam Livingston-Gray
Tuesday, February 10, 2004

Yes, you can do either.

The best way is to record the macro and then play with it.

But yes, select the data, then record the macro, that will work.

You said you don't use Excel that much, but do you use VBA much? Or are you hoping to just record the macro, and not look at any code?

I will review your initial comments and see if I can knock up some code for you.

Aussie Chick
Tuesday, February 10, 2004

The feature I always use for this is Range.CurrentRegion.

You use it like this:

Dim ACell As Range
Set ACell = Range("A1")
Set WholeTable = a.CurrentRegion

This is how Access automatically selects the current region if you do a sort or something.

More details here:

http://www.j-walk.com/ss/excel/tips/tip10.htm
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/modcore/html/deovrunderstandingrangeobject.asp

I know its not what you asked for, but I find it useful.

Ged Byrne
Wednesday, February 11, 2004

Almost all of the dialogs are available through the Application.dialogs collection.

Theres a huge list there. I've been looking myself but I can't find it.

Ged Byrne
Wednesday, February 11, 2004

Have a look at the RefEdit control in the UserForm toolbox.

gh
Wednesday, February 11, 2004

When you don't know the size of the data you really want (but you know it'll be continous - use this method when recording your macro, to keep it flexible:

select a cell at the top of your range. hold (ctrl,shift), arrow down->selects to the bottom of the filled range.

This'll translate to something like this in VBA (for columns):

Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste

pb_canuck
Wednesday, February 11, 2004