Struggling To Excel

Magically Select All The Used Areas In A Sheet using VBA

on June 15, 2014August 16, 2018 By Ejaz 14 Comments

I recently published a post about automatically formatting a table in Excel using VBA. That got me thinking, how awesome it would be, if we could format all the tables in a sheet, with a single click. For that idea to work, we need to get all the used areas in a worksheet; and then use the Areas Collection to loop through the tables. We can access the Areas Collection through the Areas property of the Range object.

Continue reading “Magically Select All The Used Areas In A Sheet using VBA”

Magically Format Tables in Excel using VBA

on June 2, 2014August 16, 2018 By Ejaz 5 Comments

Excel Tables make analyzing data, a breeze. It surprises me that it is not used as often as it should. It automatically “includes” new data you add to your spreadsheets, it automatically drags down formulas for you, it automatically formats the tables for you. In addition to that, you can use structured references that make your formulas tractable without having to name each range. You can also link an Excel Table to your PowerPivot Model. For a comprehensive, yet concise list of stuff excel tables can do, I recommend reading through this page.

Continue reading “Magically Format Tables in Excel using VBA”

Get the Actual Used Range in a Spreadsheet

on May 26, 2014August 21, 2018 By Ejaz 32 Comments

In one of my posts, I wrote about the UsedRange property of the Worksheet object. I use it in almost all of my spreadsheet applications. Excel keeps track of the last cell you used during your current session, and uses it to determine the used range of a sheet. The last cell is the one you get to, when you press Ctrl+End.

Continue reading “Get the Actual Used Range in a Spreadsheet”

Prepare for the show: A framework for hiding rows and columns in your spreadsheet applications

on April 25, 2014June 28, 2015 By Ejaz 7 Comments

Hiding a bunch of rows and columns in a sheet before showing it to your boss is inevitable. I insert blank rows and columns around a table, so I can use the CurrentRegion property of the Range Object in my code. I add labels to all my named ranges in the sheet. I split out complex formulas into a couple of columns. Ultimately I end up with a lot of rows and columns to hide. I desperately needed a framework to hide and un-hide rows and columns in all my sheets. I experimented with a lot of methods before settling down with one and I would definitely like to know if you have a better way to do it.

Continue reading “Prepare for the show: A framework for hiding rows and columns in your spreadsheet applications”

To Err is Excel, Handle your Errors with grace

on April 15, 2014June 28, 2015 By Ejaz 10 Comments

Error handling is an important aspect of programming in VBA, especially if you are writing macros for other users. Unfortunately, many users ignore it completely. Visual Basics is an amazing programming language, but it lags far behind in the error handling department. All we have is the “On Error”, “Goto” and the “Resume” statements. These statements allow only a few error handling structures, and each of the structures has its own set of expert proponents. In this post, I am going to share with you, a little block of code that I use to handle errors in all my spreadsheet applications; and hopefully offer a fresh perspective.

Continue reading “To Err is Excel, Handle your Errors with grace”

Snakes in Excel, Lets Play!

on April 6, 2014August 21, 2018 By Ejaz 11 Comments

The biggest downside to working in massive corporate offices is the locked down computer systems. There is no internet access, no games, no fun! However, Excel is always installed and kicking. How awesome would it be if Excel had a couple of games in it?

Continue reading “Snakes in Excel, Lets Play!”

Progress Bar for all your excel Applications

on March 27, 2014August 21, 2018 By Ejaz 74 Comments

Excel is versatile by itself and VBA makes it even better by allowing us to do our own thing. Most of us use VBA to automate tasks of varying complexity – some macros are executed in a flash, but others take hours to run. While there are users who are happy with just a Msgbox “This thing is DONE!”, there are others who’d like to let the user know more about what is happening.

Continue reading “Progress Bar for all your excel Applications”

Mix And Match – Get all possible permutations of an unlimited number of lists in Excel

on February 21, 2014August 26, 2018 By Ejaz 34 Comments

This post is a little fun, not much of a Struggle I guess. In excel, we use permutations more often than we realize. Say you have twenty performance metrics and you’d like to monitor them for each month, imagine how much copy pasting and concatenating would need being done to get your column header names in place?

Continue reading “Mix And Match – Get all possible permutations of an unlimited number of lists in Excel”

Concatenate Unique Cells, with Text Formatting

on February 18, 2014June 28, 2015 By Ejaz 7 Comments

A fellow struggler requested me to help him with a slightly beefed up Range Concatenate function. He needed to combine a bunch of numbers in a range; there were multiple entries of the same number, and he wanted a unique list; finally he needed to format the numbers.

Continue reading “Concatenate Unique Cells, with Text Formatting”

Initialize Local Range Names in VBA Quicker

on February 9, 2014June 28, 2015 By Ejaz 18 Comments

I always use locally named range in all my spreadsheets, in fact I wrote a post about it earlier. I extend my love for named ranges even while writing VBA code for spreadsheet applications. Using a pure Offset function based code, or a Cell Reference based code, in my opinion, is not the best way to go. Having named ranges in worksheets, and updating them to include more data before processing is the best way to go.

Continue reading “Initialize Local Range Names in VBA Quicker”