Be the first user to complete this post
|Add to List|
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
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
Check whether Row is blank and if yes delete it.
- WorkBooks and WorkSheets
- VBA-Excel: Add/Insert multiple objects from a folder in an Excel Document.
- VBA-Excel: Get all the WeekDays or Working days in Specified Date Range, (excluding Satudays and Sundays)
- VBA-Excel: Date-Time Functions – DatePart()
- VBA-Excel: Consolidator – Merge or Combine Multiple Excel Files Into One
- VBA-Excel: Date-Time Functions – DateAdd()
- VBA Excel - Cells, Ranges and Offset : Offset
- VBA-Excel: Arrays – One Dimension, Static Array