Daily posts of Excel tips…and other stuff
Archive for the ‘Range Object’ Category.
Hi all,
I’ve just published a new article on my website. Here is the introduction:
“This article explains how you can use styles to ease maintenance of your spreadsheet models.
Microsoft has made it very easy to dress up your worksheets with all sorts of fill patterns, borders and other frills. Because formatting of cells is often done [...]
I have never found a decent keyboard shortcut for filling a series, despite being the self-anointed king of all things keyboard. Oh sure, I could Alt+E, I, S, Enter, but it's just not satisfying. So in the vein of selecting adjacent columns, I added a macro to my Personal.xls.
Sub FillSeries()
[...]
This post discusses two functions I developed because of a recent need. The first is an enhanced version of the Excel Union method. The other is a Subtract function that operates on ranges.
The Union function
Those who use the Excel Application's Union method with any sense of regularity know it doesn't deal well with [...]
OK, time for some non Excel 2007 stuff...
Recently someone asked me if it was possible to transpose a table of cells, but in such a way that the formula of each cell is kept intact. I decided VBA was the way to go and produced a small but very useful little routine for that.
What the [...]
I want to move through the cells in a range using an index number (like in a For Next loop) without using a For Each statement. I have a range that is the Union of all the cells identified using the Find method. The variable rAllFound has the address:
?rAllFound.Address
$A$2,$A$4:$A$5,$A$7
There are four cells, as [...]
To select cells that aren't next to each other with your mouse, hold down the Control key while you select the cells. You can then, for instance, get the sum of those cells from the status bar. Can you do the same thing with just the keyboard? I can't figure out how, [...]
I've recently added a new macro to my Personal.xls (that's four now!). This one is to replace the cumbersome Edit > Fill > Series > Autofill (alt-e-i-s, alt-f, enter).
Sub FillSeriesAutoFill()
If TypeName(Selection) = "Range" Then
Selection.DataSeries , xlAutoFill
End If
End Sub
This uses the DataSeries method of the Range object. [...]
My wife and I split duties when it comes to teaching our kindergarten-age son. She teaches him reading and writing and I teach him math. To that end, I created a table to help him memorize adding and multiplying zero through nine. It's nothing fancy, but you can have it if you [...]
