Remove Blank Rows & Columns With This VBA Macro
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 of 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 execute it 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
Sub RemoveBlankRowsColumns()
'PURPOSE: Remove blank rows or columns contained in the spreadsheets UsedRange
'SOURCE: www.TheSpreadsheetGuru.com
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
rng.Select
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
Exit Sub
ElseIf UserAnswer = vbYes Then
StopAtData = True
End If
'Optimize Code
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
Else
If rngDelete Is Nothing Then Set rngDelete = rng.Rows(x)
Set rngDelete = Union(rngDelete, rng.Rows(x))
RowDeleteCount = RowDeleteCount + 1
End If
Next x
'Delete Rows (if necessary)
If Not rngDelete Is Nothing Then
rngDelete.EntireRow.Delete Shift:=xlUp
Set rngDelete = Nothing
End If
'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
Else
If rngDelete Is Nothing Then Set rngDelete = rng.Columns(x)
Set rngDelete = Union(rngDelete, rng.Columns(x))
ColDeleteCount = ColDeleteCount + 1
End If
Next x
'Delete Columns (if necessary)
If Not rngDelete Is Nothing Then
rngDelete.Select
rngDelete.EntireColumn.Delete
End If
'Refresh UsedRange (if necessary)
If RowDeleteCount + ColDeleteCount > 0 Then
ActiveSheet.UsedRange
Else
MsgBox "No blank rows or columns were found!", vbInformation, "No Blanks Found"
End If
ExitMacro:
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
rng.Cells(1, 1).Select
End SubAn 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
ActiveSheet.UsedRangeGo 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!
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.