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

 

Some­times delet­ing the blank rows from you Excel sheet is a tedious task to do espe­cially when your sheet con­tains lots of data, say 10k-15k rows and hav­ing some blank rows in between and you need to delete these rows. Just imag­ine to delete these blank rows man­u­ally, but VBA Codes are life saver here.

  • Open a new Excel Work­Book and press “Alt+F11” to open the Visual Basic Editor
  • Copy Paste the fol­low­ing 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

Expla­na­tion:

Dim mwb As Workbook

Set mwb = ActiveWorkbook

Get­ting the instance of Active Work­Book and stor­ing it in Work­Book reference.

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

Back­ward loop from row num­ber where last cell is typed means last cell which con­tains some data to row num­ber 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 down­loaded this huge amount of data with lots of blank rows.. Now i have a short­cut ot delete all those..

    • Test says:

      Iam get­ting error.

      Error: Basic syn­tax error.Unknown datatype abc.

      How to solve this issue?

      • sumitjain says:

        Try giv­ing proper sheet name, i have used the exam­ple as Sheets(“1”)

        Ex:

        Sub FnDelete­BlankRows()
        Dim mwb As Work­book
        Set mwb = Active­Work­book
        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: