Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Contact us
Hide Buttons

VBA-Excel: Delete Blank Rows from Excel Work Sheet

 

Sometimes deleting the blank rows from you Excel sheet is a tedious task to do especially when your sheet contains lots of data, say 10k-15k rows and having some blank rows in between and you need to delete these rows. Just imagine to delete these blank rows manually, but VBA Codes are life saver here.

  • Open a new Excel WorkBook and press “Alt+F11” to open the Visual Basic Editor
  • Copy Paste the following code
Sub FnDeleteBlankRows()
   Dim mwb As Workbook
   Set mwb = ActiveWorkbook 
   For x = mwb.Sheets("1").Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1 
      If WorksheetFunction.CountA(mwb.Sheets("1").Rows(x)) = 0 Then
         mwb.Sheets("1").Rows(x).Delete
      End If
   Next 

End Sub
  • Run the Macro

Explanation:

Dim mwb As Workbook

Set mwb = ActiveWorkbook

Getting the instance of Active WorkBook and storing it in WorkBook reference.

For x = mwb.Sheets(“1”).Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1

Backward loop from row number where last cell is typed means last cell which contains some data to row number 1.

If WorksheetFunction.CountA(mainworkBook.Sheets(“MyFirstMacro”).Rows(x)) = 0 Then

mainworkBook.Sheets(“MyFirstMacro”).Rows(x).Delete

End If

Check whether Row is blank and if yes delete it.

You may also like...

4 Responses

  1. saved a huge amount of time. just downloaded this huge amount of data with lots of blank rows.. Now i have a shortcut ot delete all those..

    • Test says:

      Iam getting error.

      Error: Basic syntax error.Unknown datatype abc.

      How to solve this issue?

      • sumitjain says:

        Try giving proper sheet name, i have used the example as Sheets(“1”)

        Ex:

        Sub FnDeleteBlankRows()
        Dim mwb As Workbook
        Set mwb = ActiveWorkbook
        For x = mwb.Sheets(“Sheet1”).Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1
        If WorksheetFunction.CountA(mwb.Sheets(“Sheet1”).Rows(x)) = 0 Then
        mwb.Sheets(“Sheet1”).Rows(x).Delete
        End If
        Next
        End Sub

  2. Chiana says:

    In awe of that anrews! Really cool!

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: