Remove Unused Rows And Columns Excel VBA Macro

Excel worksheets can accommodate data across thousands of columns and more than a million rows. However, in many occasions we use only a handful and may want to just present our data set and get rid of all the useless rows and columns. This example removes all unused rows and columns in a worksheet.

Macro/VBA code:

  Sub HideUnusedRowsCols()

      Dim lr As Long, lc As Long, lcltr As Variant

      lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

      lc = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

      lcltr = ActiveSheet.Cells(1, lc).Offset(0, 1).Address

      lcltr = Split(lcltr, "$")

      ActiveSheet.Rows(lr + 1 & ":1048576").Hidden = True

      ActiveSheet.Columns(lcltr(1) & ":XFD").Hidden = True

  End Sub

Macro explained:

• We declare two Long variables to hold the number of the last row and column with content, and another Variant variable that will be used to get the letter of the last column.
• Then we get the number of the last row (lr) and column (lc) with content using the End property as we have seen in some previous examples in this blog.
• We get the address of the first empty column using the ‘Address’ property, as we need the letter instead of the number later in the code. Note that we use Offset to move one column ahead the last column with content, thus getting the first empty column.
• Then we split the address to get only the column letter. Excel adds the $ symbol to the returned address, therefore we split based on that symbol.
• Now it’s time to hide all those unused or empty rows and columns with the Hidden property. All rows from the first empty row (lr+1) down to the last row (1048576) will be hidden. All columns from the first empty column (index 1 of the array generated when the address was split) until the last column in the worksheet (XFD) will be hidden.

We can show again all rows and columns with the following code.

  Sub ShowAllRowsCols()

ActiveSheet.Rows.Hidden = False

ActiveSheet.Columns.Hidden = False

  End Sub

This is how we remove unused rows and columns in Excel with VBA macros.

Other examples:

• How to Open Workbook Safely with Excel VBA macros
• How to Add Button To Fit Selection with Excel VBA macros 
• How to List and Link All Sheets with Excel VBA macros