Removing Blanks From Your Data
A question I see asked a great deal on Excel forums is how to delete blank cells, rows, or columns from an Excel spreadsheet. I imagine this is such a popular problem because the analyst is either:
- Try to manipulate someone else's data who doesn't know how to properly set data up in a spreadsheet, or
- They exported data from another source and are trying to format the absurd export file the program generated
Whatever the reason may be, it can become a daunting task to clean up data and this is where the ability to write/use macros is a huge advantage.
In this article, I am going to provide you with a single macro that will be able to handle a couple different scenarios and will easily allow you to clean up your spreadsheets with the click of a button. I highly recommend your store this VBA code in your Personal Macro file so you can easily executeit on any spreadsheet you're working on.
As I typically do, I will provide the code first (for those who are quite familiar with reading VBA code) and then will describe what the code does and how it can make you more efficient.
The VBA Macro Code
'PURPOSE: Remove blank rows or columns contained in the spreadsheets UsedRange
Dim rng As Range
Dim rngDelete As Range
Dim RowCount As Long, ColCount As Long
Dim EmptyTest As Boolean, StopAtData As Boolean
Dim RowDeleteCount As Long, ColDeleteCount As Long
Dim x As Long
Dim UserAnswer As Variant
'Analyze the UsedRange
Set rng = ActiveSheet.UsedRange
RowCount = rng.Rows.Count
ColCount = rng.Columns.Count
DeleteCount = 0
'Determine which cells to delete
UserAnswer = MsgBox("Do you want to delete only the empty rows & columns " & _
"outside of your data?" & vbNewLine & vbNewLine & "Current Used Range is " & rng.Address, vbYesNoCancel)
If UserAnswer = vbCancel Then
ElseIf UserAnswer = vbYes Then
StopAtData = True
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
'Loop Through Rows & Accumulate Rows to Delete
For x = RowCount To 1 Step -1
'Is Row Not Empty?
If Application.WorksheetFunction.CountA(rng.Rows(x)) <> 0 Then
If StopAtData = True Then Exit For
If rngDelete Is Nothing Then Set rngDelete = rng.Rows(x)
Set rngDelete = Union(rngDelete, rng.Rows(x))
RowDeleteCount = RowDeleteCount + 1
'Delete Rows (if necessary)
If Not rngDelete Is Nothing Then
Set rngDelete = Nothing
'Loop Through Columns & Accumulate Columns to Delete
For x = ColCount To 1 Step -1
'Is Column Not Empty?
If Application.WorksheetFunction.CountA(rng.Columns(x)) <> 0 Then
If StopAtData = True Then Exit For
If rngDelete Is Nothing Then Set rngDelete = rng.Columns(x)
Set rngDelete = Union(rngDelete, rng.Columns(x))
ColDeleteCount = ColDeleteCount + 1
'Delete Columns (if necessary)
If Not rngDelete Is Nothing Then
'Refresh UsedRange (if necessary)
If RowDeleteCount + ColDeleteCount > 0 Then
MsgBox "No blank rows or columns were found!", vbInformation, "No Blanks Found"
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
An Overview Of What This VBA Code Does
Let's do a quick walk-through of what this code will do and the various prompts that you will need to answer.
- Make sure the sheet you want to delete blanks is active (ie the worksheet being displayed on your computer screen currently)
- You will be prompted to decide whether you want the macro to delete all blank columns/rows or to just delete the columns/rows that are outside your data set containing invisible data (I call these ghost cells because they don't appear to have any data in them, but they are still viewed in Excel's mind as data-carrying cells)
- Finally, after the VBA macro makes its deletions, it will recalculate the Used Range. This will allow your scroll bar and Used Range dimensions to readjust themselves according to the data that is left on the spreadsheet.
Now that you understand what the code is accomplishing, let's dig a little deeper and learn why the code is doing the actions described above.
Why Does My Data Warehouse Software Export Like This?
Most clean-up jobs originate from the weird formats that outside programs export their data to. I don't know if this stems from a care-free attitude for formatting, how different versions of Excel receive in data, or just pure laziness. Whatever it is, it's ANNOYING! Whew, that felt good to say. Anyways...let's take a look at how the macro code in this article can transform the report on the left.
With a few clicks of the mouse, the macro transformed our ugly-looking data into something much more manageable. No one should be doing this manually. As an alternative, there are ways to quickly delete rows by using helper columns, formulas, & a little bit of filtering; I however, prefer Excel doing all the work for me.
Blank Cells Outside Of Your Data
Ever try to use the shortcut Ctrl + Shift + Down Arrow to select all of your data and the selection includes a bunch of blank rows or columns? This is because those cells once held data, but only the cell values were removed, not the cells themselves (Hint: using your delete key does not delete the cell, it only deletes the cell's value). Every cell in your spreadsheet that has ever been modified, whether by keying in a value or simply filling the cell with the color yellow, will trigger Excel to store a whole slew of data about that cell and incorporate into the "Used Range". The only way to get Excel to forget about it is to delete the cell by right-clicking on the cell and selecting delete.
Why Is My Scroll Bar So Small?!
One of my biggest pet peeves is having a scroll bar that acts like there are 10,000 rows of data when there are only 100 rows. This occurs because the scroll bar's "scroll range" is based on the dimensions of Excel's Used Range (which may contain ghost cells). The only way to readjust your scroll bar's range manually is to delete all of those ghost cells and SAVE your workbook. Saving is the trigger that tells the scroll bar to recalculate its range. This is the step I find most people don't realize and they get confused because their scroll bar still looks janky. Below is an example of what I hate seeing:
As Alex so kindly pointed out in the comments section, you can use VBA to refresh the Used Range without saving. Just use the following VBA command:
'Refresh the current worksheet's Used Range
Go Forth And Clean Some Data
Now you have a tool that can be a real time saver on a daily basis. Hopefully, I coded the VBA in versatile way that will suit your needs, but please leave a comment below if you have any suggestions for improvement or additional functionality. As always, I enjoy hearing your feedback. So if you have a couple minutes leave a note below about your experience with this article's code or anything about the website in general. Hopefully, you are well on your way to becoming your office's expert!
How Do I Modify This To Fit My Specific Needs?
Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it!
I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).
I wish you the best of luck and I hope this tutorial gets you heading in the right direction!
Chris "Macro" Newman :)