What should you do when you need to delete blank rows, but you don’t know exactly how many columns or rows your data range will have?
The GIF below walks through 3 differently-sized data ranges. In the first run, there are NO blank rows, so nothing happens. Nice! The second run looks at a smaller range, and in this case the Barry Bonds row is now empty — so that row gets deleted. Finally, the third run looks at an even smaller range, where Jonathan Moore, Vidhan Argesh and Barron Caster are all now blank — and each row is deleted as such. Flexibility y’all!
Lines 4-10 handle Step 1 – Setup, where we declare variables and establish the Worksheet we will be operating on.
Lines 12-20 are our Step 2 – Exploration section. Even though dynamic spaces can be hell on your brain, you can actually make short work of them in a situation like this with two simple variables: the last row and the last column. Here, we find them and assign them to lngLastRow and lngLastCol.
The last row (lngLastRow) sets the limits for our “outer” loop, where we’ll navigate through the rows. The last column (lngLastCol), on the other hand, sets the limits for our “inner” loop, where we’ll navigate through the columns.
Lines 25-51 are the Step 3 – Execution here. There’s a lot happening here, so let’s break it down:
For lngIdx = lngLastRow To 1 Step -1
This is our “outer” loop, which navigates the rows. WHEN IT COMES TO DELETING ROWS ONE-BY-ONE, YOU ALWAYS NEED TO START FROM THE BOTTOM AND WORK YOUR WAY UP. Looping from top-to-bottom will leave you in a world of hurt, as the For loop counter will be off as soon as you delete the first row.
The next two lines initialize two variables for use in our “inner” loop: blnAllBlank, which we’ll use to flag non-blank cells, and lngColCounter, which we’ll increment as we walk through the columns in the row.
The next line kicks off a While loop, which is perfect for situations like this where we have multiple break conditions:
While blnAllBlank And lngColCounter <= lngLastCol
Let’s dissect that.
First of all, if we encounter a cell that is NOT blank in a row, we can break this loop immediately — right? We only want to delete rows that are entirely blank, so the presence of a single NON-blank cell means that row should not be deleted.
The second condition checks to make sure that we are never examining cells beyond the last-occupied column number, which is stored in lngLastCol. This check ensures that our While loop does not go on forever! The loop will always quit when the lngColCounter exceeds the lngLastCol.
Inside the While loop, we’re running an If statement that checks whether or not a given cell is empty. If the cell IS empty, we increment our column counter (lngColCounter) by one.
Once the While loop finishes, we have one last If, and this is where we delete the row (or not). By looking at the blnAllBlank flag, the decision is easy — if the blnAllBlank flag is True, we know that everything in the row is blank and the row should be deleted! Otherwise, we move up to the next row.
Finally, line 54 is our one-line Step 4 – Cleanup, where we simply notify the user that the script has finished. Easy cheesy!
Is your dynamic, deletes-blank-rows-as-it-goes macro working the way you want? If not, let me know and I’ll help you get what you need! And if you’d like more step-by-step, no-bullshit VBA guides delivered direct to your inbox, join my email newsletter below.
What should you do when you need you need to combine data but write the output into rows, not columns?
99 times out of 100, a pivot table is the answer to your data aggregation needs. But what about getting your output into row format — and, more specifically, what if you need each item in a new column? Consider a list of camper names and their assigned cabins — what if you need cabins on rows, with each assigned camper in a new column?
That’s a lot harder than just dropping in a pivot table, but with a little VBA we can make it happen.
First things first — the code below relies on the Dictionary object, when is not part of the standard VBA suite! Don’t let that scare you though, as you can add it with a couple clicks.
From the VBA window, click Tools > References..., then check the box next to “Microsoft Scripting Runtime” and you’re done. Here’s a gif of showing you how to do that:
Now that you’ve enabled this library, the code below will create a new “cabins” sheet with each camper in his or her own column.
Lines 4-19 handles Step 1 – Setup. We’re dimensioning our variables, assigning worksheets and creating a new worksheet named “cabins”. (Note: if you already have a “cabins” sheet, this macro will fail — so make sure you delete that sheet if you want to re-run the code!)
Lines 22-43 is our Step 2 – Exploration, which is usually more in-depth than the Setup. First, we need to identify our data “limit”, which is the last occupied row. We assign that value to lngLastRow, then run a For loop over the campers and cabins, populating a Dictionary object for each.
dicCampers <- this Dictionary holds all the camper names as Keys, with the camper’s cabin as Items dicCabins <- this Dictionary holds all the cabin names as Keys AND Items
These Dictionary objects are what we’ll loop through in the next step, Execution.
Lines 46-67 handle our Step 3 – Execution. There are two loops running here, which can be confusing, so let’s get into the nitty gritties. Here’s the outer loop:
For Each varCabin In dicCabins.Keys
dicCabins is a Dictionary containing the unique cabin names, so it’s small. In the gif above, there are only 5 cabins: “Singers”, “Guitarists”, “Bassists”, “Drummers” and “Pianists”. Each of these cabin names belongs on its own row, so we increment the lngCabinRow counter by one each time this loop completes.
The inner loop is bigger:
For Each varCamper In dicCampers.Keys
dicCampers is a Dictionary containing all the camper names (as Keys), with each camper’s cabin stored as the Item. Here, the loop check’s each camper’s cabin — if the cabin matches active cabin from dicCabins, then we write that camper into the row.
Phew!
Finally, line 70 is our super-simple Step 4 – Cleanup, where we notify the user that the script has completed.
With that, you’ve got your campers listed in columns, with a row for each cabin. Nice!
Did this script and transform work for you? If not, let me know and I’ll help you get what you need! And if you’d like more step-by-step, no-bullshit VBA guides delivered direct to your inbox, join my email newsletter below.
What should you do when you get numbers (like YYYYMMDD) instead of dates in your data file?
Do you remember that time your data source was absolutely perfect? It was amazing — you dropped those rows straight into Excel and boom, everything was humming along…
EXCEPT THAT NEVER HAPPENED. NOT EVER. NOT EVEN ONCE.
Broken dates are at the top of the Analyst shit list. If your dates are coming in as numbers formatted YYYYMMDD (like 20150610), we’re going to fix them for good with the function in the GIF above.
The beauty of a Public Function like the above is that you can call it straight from the worksheet, like any other formula! Your fellow Analysts will love you for it.
Even though this Function only has 3 lines, all 4 steps are present. Let’s break it down:
Line 3 is our Step 1 – Setup — all we need a single variable.
Line 6 is our Step 2 – Exploration. We take the .Text of the Range that was passed in and assign it to our single variable.
Line 9 is handles both the Step 3 – Execution and Step 4 – Cleanup. There’s a lot going on here, so let’s go piece-by-piece:
DateSerial takes a year, month and day and converts them into an Excel Date
Left is a straightforward text-slicer, which takes a String the numbers of characters to slice.
Mid is a lot like Left except it takes one extra parameter. First, you pass in a String, then the number of characters in (from the left) that you’d like the slice to start, and finally the number of characters.
Right is EXACTLY like Left, except it starts from the right haha.
By shuffling the order of Left, Mid and Right, you can convert any nasty date number garbage that comes your way.
And finally, Step 4 – Cleanup is that last assignment. When you want your Function to return something in VBA, you need to set the Function name equal to the output, like Line 9 does.
And that’s it — numerical dates can now be converted to Excel Dates by simply calling =CONVERT_YYYYMMDD_TO_DATE from a cell, like you would call =SUM or =AVERAGE. Nice!
Is your number-to-date function running like a champ? If not, let me know and I’ll help you get what you need! And if you’d like more step-by-step, no-bullshit VBA guides delivered direct to your inbox, join my email newsletter below.
What should you do when you need to insert a complicated calculation to a new column at the end of your data file?
Inserting a column of complicated calculations at the end of your data block is something you’ll do CONSTANTLY as an Analyst, but there are a million ways to add a column of values. Let’s come up with a single solution that is:
Fast
Easy to tweak in the future
Sound good? Good!
Here’s the complete subroutine used in the GIF at the top of this post — an in-depth explanation for each section is included below the code.
First, use Option Explicit… seriously. It will force you to declare all your variables, which makes catching typos a breeze. Writing VBA without Option Explicit is Hard Mode — fuck that. Turn on Option Explicit.
Second, declare all your variables in one place explicitly. This gives you a single, easy-to-read identify space for your variables, which makes it a cinch to avoid type errors.
Got Option Explicit turned on and your variable declarations in one place? Sweet! Let’s get into the juicy stuff.
Lines 4-11 handle Step 1 – Setup, as we declare variables and establish the Worksheet we will be operating on.
Lines 13-21 are our Step 2 – Exploration section, where we determine the last row and set up a Range that contains all the cells from A1 to the last occupied row in column A.
Lines 27-56 are the Step 3 – Execution part, which is where most of the cool stuff happens. Variant Arrays are beautiful because you can simply equate them to a Range, like on line 27.
varCategory instantly became a 1-column Array of all the values from rngCategory, which was defined on line 20.
The first letter is easy to grab on line 34, where we use:
UCase (which capitalizes all letters)
CStr (which converts the cell value into a String)
Left (which takes the left-most characters)
We use a Select Case statement, which begins on line 35, to handle many If conditionals with a much-easier-to-extend syntax.
Finally, we write from the varResults Array (which we filled up with the loop and Select Case) back to the Worksheet from line 47 to 56.
Line 59 is our super-simple Step 4 – Cleanup — there isn’t any real “clean up” needed, so we just prompt the user that the macro has finished. Boom!
Do you have a super-fast column-adding script up-and-running? If not, let me know and I’ll help you get what you need! And if you’d like more step-by-step, no-bullshit VBA guides delivered direct to your inbox, join my email newsletter below.
