Remove Filters in an Excel Sheet using VBA


Has your data import procedure ever failed due to users applying filters to the data? This happens quite often, especially when you have to distribute multiple files, which you intend to stack later. Copying filtered data copies only works on the visible cells; and you end up loosing out the hidden rows if the user applied a filter before saving the file. Let us take a look at how to remove filters from an Excel sheet from VBA, so you can copy the whole dataset.

Fit Worksheet to Window in Excel


Have you ever felt Excel needed a Fit-Document-to-Window-Width feature like the millions of PDF Readers out there? Wouldn’t it be great if you could automatically fit your document to your current window size? The irony is, Excel already has all it needs to deliver that feature to you. Since spreadsheets come in various shapes andContinue reading “Fit Worksheet to Window in Excel”

Duplicate a Sheet and retain a reference to it in Excel VBA


I recently worked on a project where my client had a Master template-sheet that needed to be duplicated and populated repeatedly. I needed to duplicate a sheet, and retain a reference to it, so I can rename it, reposition it, and pass it to another sub that populates it with data. I assumed there mustContinue reading “Duplicate a Sheet and retain a reference to it in Excel VBA”

How to deal with the Numbers Stored As Text Error in Excel?


How many times have you encountered the “Numbers Stored as text” error in your data sets? It interferes with your LOOKUP and MATCH functions, and arithmetic calculations. Excel has a Convert to Number functionality to help with this situation, but it could be a lot better. You have to deal with your columns one at a time, sometimes one cellContinue reading “How to deal with the Numbers Stored As Text Error in Excel?”

Magically Select All The Used Areas In A Sheet using VBA


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 loopContinue reading “Magically Select All The Used Areas In A Sheet using VBA”

Magically Format Tables in Excel using VBA


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 formulasContinue reading “Magically Format Tables in Excel using VBA”

Get the Actual Used Range in a Spreadsheet


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 pressContinue reading “Get the Actual Used Range in a Spreadsheet”

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


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 outContinue 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


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 onlyContinue reading “To Err is Excel, Handle your Errors with grace”

Progress Bar for all your excel Applications


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 thingContinue reading “Progress Bar for all your excel Applications”