Remove Blank Rows & Columns With This VBA Macro

By Chris Newman •  Updated: 03/08/15 •  7 min read
Using VBA Macro Code To Delete Remove Blank Rows Columns

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:

  1. Try to manipulate someone else's data who doesn't know how to properly set data up in a spreadsheet, or
  2. 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

  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
        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
        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
  End If

'Refresh UsedRange (if necessary)
  If RowDeleteCount + ColDeleteCount > 0 Then
    MsgBox "No blank rows or columns were found!", vbInformation, "No Blanks Found"
  End If

  Application.Calculation = xlCalculationAutomatic
  Application.EnableEvents = True
  rng.Cells(1, 1).Select
End Sub

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.

  1. Make sure the sheet you want to delete blanks is active (ie the worksheet being displayed on your computer screen currently)
  2. 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)
  3. 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.

Clean Up Outside Data Program Exports With VBA Macro Automation

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

There can be blank cells that show up in the Used Range, I call this 'Ghost 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:

Why Is my scroll bar so small compared to spreadsheet data

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!

Keep Learning

Chris Newman

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.