Moving around Excel efficiently in code

Let’s face it, Excel is horrible at iteration over cells. Somewhat like in Matlab where a loop over entries will kill your processing time, you don’t want to move from cell to cell reading information and processing unless you have to.

Instead, the recommended procedure is to work with ranges, blocks of cells.I had written examples of how to pull data off excel using COM and yanking data in larger ranges. In most cases however, you won’t be working with tables that take up all 16.8Million cells. The tables will probably be a few thousand rows, maybe a hundred or so columns. still a formidable amount of information, but much easier to work with.

The question is how do you generate portable code that can handle tables of different sizes. Hardcoding against table addresses is a surefire way of having to adjust the code every time someone so much as sneezes on the file.

In comes playing with Ranges and End() and so on.

Take this example graphic:

Starting at cell A1, we would like to take the table defined by the corners A1 in the top left, and Y in the bottom right. This is trivial to do at the keyboard with excel in front of you. Click D1 once, hold Ctrl+shift, left arrow, then while still holding, down arrow. You can’t click on A1 since there’s nothing down in A, so Ctrl+shift+down would select the whole column.

What we’d like to do is to do that in code.

If you record a macro you’d get:

1
2
3
4
5
Sub Macro1()
Range("D1").Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlDown)).Select
End Sub

This of course works, but as usual the code snippet has extraneous steps, such as selecting a cell and working with the selection. all that’s really important is that we notice that Range() can take 2 arguments, both of which are ranges. In the above example Selection and Selection.End(xlToLeft) both are, or return, range objects. Range() then does the rest of the  work for us and makes the connecting box for us.

What we want to do is use the  .End() to navigate around. It behaves exactly like a Ctrl+Arrow key does in the spreadsheet, stopping at the edges of tables and things. This allows us to write flexible code quickly.

All we need to do is feed .End() with the built in Excel constants  xlDown,xlToLeft, xlToRight, and xlUp. We can boil down the action to a single line (if we really had to)

Range( Range("d1",Range("d1").End(xlToLeft)) , Range("d1").End(xlToLeft).End(xlDown)).Select

It’s ugly, and not really clear thanks to the nesting. But once you realize that all you need are Range objects, and Range objects are being returned to you, you can compose as you want.

In python

Doing the same things in python is just as easy, except that we don’t have access to those magical built in constants. What to do? Well, you look inside the VBA documentation, there’s a huge number of constants in there, and once you identify what you need, you can store them.

Personally, I keep a file lying around with an xlConstants dictionary that stores all the ones I tend to use. The values tend not to make much logical sense at times, so don’t bother memorizing, or hard coding them.

xlConstants = {
#xlDirection
'xlDown':  -4121,
'xlToLeft':  -4159,
'xlToRight':  -4161 ,
'xlUp':  -4162,
}
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>