To buy Bob's book: Excel Outside the Box
Today, a guest appearance by Excel MVP Bob Umlas. Say you have random chunks of data and need to add a total to each chunk. Bob shows off a clever VBA solution that adds the totals in almost one line of code. Bob is the author of Excel Outside the Box.
If you want to solve this problem without using VBA, see Kevin Lehrbass's video at
Today, a guest appearance by Excel MVP Bob Umlas. Say you have random chunks of data and need to add a total to each chunk. Bob shows off a clever VBA solution that adds the totals in almost one line of code. Bob is the author of Excel Outside the Box.
If you want to solve this problem without using VBA, see Kevin Lehrbass's video at
Transcript of the video:
Learn Excel from MrExcel podcast episode 1832.
Bob Umlas Put in Totals with VBA even though it's chaotic data.
Hey, everyone welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today I have something special i have a guest post from Bob Umlas.
Bob is the author of this great book, Excel outside the box Unbelievable Excel Techniques.
There are 54 amazing techniques in this book.
I'm going to put the link where you can buy the book right down there in the YouTube comments.
So, check it out all right. Bob.
Hi! This is Bob Umlas talking to you from the summit and good old Washington State.
All right, here's a worksheet very simple supposing what you want to do is put in subtotals, you want to subtotal, I'm in a total here in cell C4 and the total here and a total here and do it in VBA let's just say this goes on and on the solution is already done in this button, but I want to show you a couple of things about it.
One, you might think that this is a little complicated because in this case we looks like we have two blank lines and then here we have three blank lines and this most of them are three columns with this one is four columns.
So, it looks like it have to really analyze the data, but in fact aside from the sub and end sub and the for next loop this is done in one statement and it's also quite instant.
You see, it's done already.
I want you to just notice something here that what this data is and I'm going to undo all these.
So, we put it back to the way it was originally these are all the formulas.
So, wonder if I branch to a special and I go to all the constants not just numbers with text, but all the constants you'll notice that there are like four pieces or four chunks of things and as long as you know your data is in pieces you can work with each piece at a time.
So, each piece has a certain number of columns and rows and if I use, I'm actually using the word chunk in the Macro.
So, for each chunk in each one of these areas there we have four areas here we can know the number of columns in the area or in the chunk and the number of rows in the chunk and that makes it able to be done all in one statement.
So, let's look at assign Macro and I'll edit this PIT which stamps are put in totals and here's the total.
So, sub put in totals for each chunk and the chunk is each one of those areas in the cells that special cells all the constants because there are no formulas in there and the key thing here is that each one is an area so, each chunk as it goes through is one of these areas.
So, that area or chunk offset by the number of rows in that chunk.
chunk.Rows.Count and then offset by the number of columns in that in the trunk also minus 1 and minus 1 because they're using the offset and I'm resizing that whole thing one by one and we'll take a look at what that is and in the background, but that formula is and we'll go back to this in a moment the sum of the row that I'm in and the chunk, the chunks row and the column that I'm in through the row above where I'm at.
So, let's take a look at the first chunk which is A1 through C3.
So, chunk,rows.count is 3 chunk.columns.count is 3.
So, this is 3-1.
I'm sorry 3, 3 comma 2 chunk that offset 3 2 this is the chunk offset by 3 is 1 2 3 down here 1 2 3 and offset by three columns 1 2 3, but the minus 1 takes you here.
So, that's the cell I'm in currently it has the same shape though is still a 3 by 3 shape that's why I say resize one by one and that formula is the cell that I'm in, the column that I'm in, the chunk.row is Row 1 and through the row that I'm in, above the row I'm in.
So, you can see that each one of these this is sum of C1 through C3 which is the row above D6to D9 and so on.
Works pretty cool.
Bill: All right, there you go.
Now, Bob has been a Microsoft Excel MVP longer than any MVP he was there when the program started a great way to simplify that task the book has more than just VBA though and we'll have more podcasts from Bob over the coming weeks.
So, check it out again the link right down there in YouTube comments.
Wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Bob Umlas Put in Totals with VBA even though it's chaotic data.
Hey, everyone welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today I have something special i have a guest post from Bob Umlas.
Bob is the author of this great book, Excel outside the box Unbelievable Excel Techniques.
There are 54 amazing techniques in this book.
I'm going to put the link where you can buy the book right down there in the YouTube comments.
So, check it out all right. Bob.
Hi! This is Bob Umlas talking to you from the summit and good old Washington State.
All right, here's a worksheet very simple supposing what you want to do is put in subtotals, you want to subtotal, I'm in a total here in cell C4 and the total here and a total here and do it in VBA let's just say this goes on and on the solution is already done in this button, but I want to show you a couple of things about it.
One, you might think that this is a little complicated because in this case we looks like we have two blank lines and then here we have three blank lines and this most of them are three columns with this one is four columns.
So, it looks like it have to really analyze the data, but in fact aside from the sub and end sub and the for next loop this is done in one statement and it's also quite instant.
You see, it's done already.
I want you to just notice something here that what this data is and I'm going to undo all these.
So, we put it back to the way it was originally these are all the formulas.
So, wonder if I branch to a special and I go to all the constants not just numbers with text, but all the constants you'll notice that there are like four pieces or four chunks of things and as long as you know your data is in pieces you can work with each piece at a time.
So, each piece has a certain number of columns and rows and if I use, I'm actually using the word chunk in the Macro.
So, for each chunk in each one of these areas there we have four areas here we can know the number of columns in the area or in the chunk and the number of rows in the chunk and that makes it able to be done all in one statement.
So, let's look at assign Macro and I'll edit this PIT which stamps are put in totals and here's the total.
So, sub put in totals for each chunk and the chunk is each one of those areas in the cells that special cells all the constants because there are no formulas in there and the key thing here is that each one is an area so, each chunk as it goes through is one of these areas.
So, that area or chunk offset by the number of rows in that chunk.
chunk.Rows.Count and then offset by the number of columns in that in the trunk also minus 1 and minus 1 because they're using the offset and I'm resizing that whole thing one by one and we'll take a look at what that is and in the background, but that formula is and we'll go back to this in a moment the sum of the row that I'm in and the chunk, the chunks row and the column that I'm in through the row above where I'm at.
So, let's take a look at the first chunk which is A1 through C3.
So, chunk,rows.count is 3 chunk.columns.count is 3.
So, this is 3-1.
I'm sorry 3, 3 comma 2 chunk that offset 3 2 this is the chunk offset by 3 is 1 2 3 down here 1 2 3 and offset by three columns 1 2 3, but the minus 1 takes you here.
So, that's the cell I'm in currently it has the same shape though is still a 3 by 3 shape that's why I say resize one by one and that formula is the cell that I'm in, the column that I'm in, the chunk.row is Row 1 and through the row that I'm in, above the row I'm in.
So, you can see that each one of these this is sum of C1 through C3 which is the row above D6to D9 and so on.
Works pretty cool.
Bill: All right, there you go.
Now, Bob has been a Microsoft Excel MVP longer than any MVP he was there when the program started a great way to simplify that task the book has more than just VBA though and we'll have more podcasts from Bob over the coming weeks.
So, check it out again the link right down there in YouTube comments.
Wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
