Don't select what I selected, select what YOU think you should select!

January 2, 2012 4:51 PM

Help me understand how to make a macro do what I want it to do - automatically select the relevant area instead of a previously recorded cell range.

I run lots of reports every month that get dumped into Excel and I try to use macros for as much as I can - but I can't figure this one out. If have only one cell selected and click 'data sort' or 'create pivot table' then the selection is expanded to all the cells that have information in them. This is 99% of the time exactly what I want, so that's what I do.

If I record this action as a macro, though, and then run it again later on a spreadsheet with fewer or more rows than the original then it only selects exactly the size of the data that was present when recording the macro. I know just enough to go in and look at the lines of the macro and see where it says things like "select A2:G411".

How do I change that to say "do the magic you do when I just ask you to select what you think is relevant"?

posted by komara to Computers & Internet (6 answers total)

It's been a long time since I've dealt with macros in Excel, but as I recall you need to define a Named Range.

See here for more info.
posted by dfriedman at 5:12 PM on January 2, 2012

Best answer: Try using the CurrentRegion property. For example, Range("C1:D1").Select would select cells C1 and D1 whereas Range("C1").CurrentRegion.Select will select all the cells that have something in them and are in contact with cell C1. There is more information here: Using the CurrentRegion Property to Quickly Select a Data Range
posted by Jasper Friendly Bear at 7:49 PM on January 2, 2012

Response by poster: Hey Jasper Friendly Bear, that totally worked! Thank you for that particular bit of magic!
posted by komara at 3:38 PM on January 3, 2012

Response by poster: well ... now I'm doing a different part (where it selects the entire set of data and sorts by various columns) and I have:

ActiveWorkbook.Worksheets("WU").Sort.SortFields.Add Key:=Range("A3:A3829"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

and I don't know if I can just change that to be:

ActiveWorkbook.Worksheets("WU").Sort.SortFields.Add Key:=Range("A3").CurrentRegion, _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

for each instance of Range in that particular macro. Does it work like that?
posted by komara at 3:44 PM on January 3, 2012

Unfortunately not. Lets assume your data stops at column G and row 3829. When the macro is run, Excel translates the Range("A3").CurrentRegion part to Range("A3:G3829"). Similarly, Range("B3").CurrentRegion would get translated to Range("A3:G3829"). This is because CurrentRegion picks up all of the cells that are contiguous with the starting range i.e. moving outwards in all directions until it encounters the edge of the worksheet or a blank row/column. This isn't too useful for our purposes as we'll always get the same table of data rather than the single, specific column that we want to sort on.

One possible solution is to simply use the CurrentRegion bit in order to get the correct number of rows and use that to help specify the column we want:

ActiveWorkbook.Worksheets("WU").Sort.SortFields.Add Key:=Range("A3:A" & Range("A3").CurrentRegion.Rows.Count), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

Similarly, sorting on row B might be:

ActiveWorkbook.Worksheets("WU").Sort.SortFields.Add Key:=Range("B3:B" & Range("A3").CurrentRegion.Rows.Count), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

This is because Range("A3").CurrentRegion.Rows.Count gets translated to 3829 and we're just tacking that on the end of the column letters that we're interested in.

It's also possible to use the Resize and Offset methods to restrict the CurrentRegion range to a specific column but I think that the above option might be the more straightforward one
posted by MUD at 5:25 PM on January 3, 2012

Response by poster: As long as I have you all on the line I have a new macro question.

When I hit CTRL-N it creates a new file called Book2, Book3, Book4, etc. I'm sure you're familiar with that, how every time you create a new thing in Excel it increments the number.

One of my current tasks is sorting a list of customer information, taking one particular customer's information out of it and pasting it into a new file, then creating a pivot table.

I want to automate the creation of this pivot table, or at least as much as I can. However, all my macros end up recording the PivotTable7 or PivotTable14 or whatever number test I'm on. It's unwieldy for me to close the main customer list and exit Excel every time that I want to cut out some information just so that the pivot table is named PivotTable1, you know?


Right now I've solved this by right-clicking the pivot table, choosing options, and renaming it to a specific string, and running the macro which says "perform these operations on the pivot table named 'foo'." Is there a more elegant way to do this? To be able to say, "hey, look, just find the one single pivot table in this file and perform these operations on it"?
posted by komara at 12:24 PM on January 4, 2012