<<Convert Excel Spreadsheets to Web Pages \| Trading Software That Operates Within Excel \| Convert Excel, Access & Other Databases \| Merge Excel Files>>

Excel: Delete/Deleting Blank Rows With Excel VBA

| | Information Helpful? Why Not Donate.

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL| More Free Downloads.. Best Value: Finance Templates Bundle

Conditional Excel Row Deleting-Delete Rows Based on Criteria

Delete rows by condition add-in.

Below are 6 methods that will delete rows from within a selection. If you know the range you can replace "Selection" with your Range(). It is important to note that the least efficient methods involve those that useloops. This is because they only delete one row at a time!

In some examples we turn off Calculation and Screenupdating. The reason we turn off calculation is in case the range in which we are deleting rows contains lots of formulas, if it does Excel may need to recalculate each time a row is deleted, slowing down the macro. The screenupdating being set to false will also speed up our macro as Excel will not try to repaint the screen each time it changes.

Subs: DeleteBlankRows1, DeleteBlankRows3 and both Worksheet_Change events are slightly different as they first check to see if the ENTIRE row is blank.

Sub DeleteBlankRows1()'Deletes the entire row within the selection if the ENTIRE row contains no data.'We use Long in case they have over 32,767 rows selected.Dim i As Long 	'We turn off calculation and screenupdating to speed up the macro.	With Application		.Calculation = xlCalculationManual		.ScreenUpdating = False      	'We work backwards because we are deleting rows.	For i = Selection.Rows.Count To 1 Step -1		If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then			Selection.Rows(i).EntireRow.Delete		End If	Next i		.Calculation = xlCalculationAutomatic		.ScreenUpdating = True 	End WithEnd Sub

Sub DeleteBlankRows2()'Deletes the entire row within the selection if _ some of the cells WITHIN THE SELECTION contain no data.On Error Resume NextSelection.EntireRow.SpecialCells(xlBlanks).EntireRow.DeleteOn Error GoTo 0End Sub

Sub DeleteBlankRows3()'Deletes the entire row within the selection if _the ENTIRE row contains no data.Dim Rw As RangeIf WorksheetFunction.CountA(Selection) = 0 Then   MsgBox "No data found", vbOKOnly, "OzGrid.com"   Exit SubEnd If    With Application        .Calculation = xlCalculationManual        .ScreenUpdating = False    Selection.SpecialCells(xlCellTypeBlanks).Select        For Each Rw In Selection.Rows            If WorksheetFunction.CountA(Selection.EntireRow) = 0 Then                Selection.EntireRow.Delete            End If        Next Rw        .Calculation = xlCalculationAutomatic        .ScreenUpdating = True    End WithEnd Sub

Sub MoveBlankRowsToBottom()'Assumes the list has a heading	With Selection		.Sort Key1:=.Cells(2, 1), Order1:=xlAscending, _			Header:=xlYes, OrderCustom:=1, MatchCase:=False, _			Orientation:=xlTopToBottom	End With End Sub

Sub DeleteRowsBasedOnCriteria()'Assumes the list has a heading.   	With ActiveSheet             If .AutoFilterMode = False Then .Cells(1, 1).AutoFilter                    .Range("A1").AutoFilter Field:=1, Criteria1:="Delete"                    .Range("A1").CurrentRegion.Offset(1, 0).SpecialCells _			(xlCellTypeVisible).EntireRow.Delete     		.AutoFilterMode = False	End WithEnd Sub

Sub DeleteRowsWithSpecifiedData()'Looks in Column D and requires Column IV to be clean	Columns(4).EntireColumn.Insert	With Range("D1:D" & ActiveSheet.UsedRange.Rows.Count) 			.FormulaR1C1 = "=IF(RC[1]="""","""",IF(RC[1]=""Not Needed"",NA()))"			.Value = .Value			On Error Resume Next			.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete	End With	On Error GoTo 0	Columns(4).EntireColumn.DeleteEnd Sub

To use any or all of the above code:

Open Excel.
Push Alt+F11 to open the VBE (Visual Basic Editor).
Go to Insert>Module.
Copy the code and paste it in the new module.
Push Alt+Q to return to Excels normal view.
Push Alt+F8 and then select the macro name and click Run. Or select Options and assign a shortcut key.

Removing Blank Rows Automatically

The codes above will work fine for removing blank rows from a list that already has some, but as the saying goes "Prevention is better than cure". The two examples below will remove blank rows as they occur. Either code should be placed within the Worksheet module and will occur each time a cell changes on the worksheet.

In both codes you will notice the Application.EnableEvents=False this is often needed within Event codes like this, else the Event will be triggered again once the code executes which in turn will again trigger the Event and so on.....

You will no doubt also notice the GoTo SelectionCode which occurs if the number of cells within the selection exceeds one. The reason for this is an error would occur if the code reached the Target keyword as Target refers to a single cell.

The second example uses the Sort method rather than the EntireRow.Delete and is the preferred method to use if possible. What happens is, any blank rows are placed at the bottom of the range should the entire row be blank.

The use of the keyword Me is a good habit to get into when working within Worksheet and Workbook modules. This was shown to me by my internet friend from Belgium, Geert Dumortier.

Private Sub Worksheet_Change(ByVal Target As Range)'Deletes blank rows as they occur.	'Prevent endless loops	Application.EnableEvents = False	'They have more than one cell selected	If Target.Cells.Count > 1 Then GoTo SelectionCode		If WorksheetFunction.CountA(Target.EntireRow) = 0 Then			Target.EntireRow.Delete		End If	Application.EnableEvents = True	'Our code will only enter here if the selection is more than one cell.	Exit Sub			SelectionCode:	If WorksheetFunction.CountA(Selection.EntireRow) = 0 Then		Selection.EntireRow.Delete	End If	Application.EnableEvents = TrueEnd Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)'Sorts blank rows to the bottom as they occur	'Prevents endless loops	Application.EnableEvents = False	'They have more than one cell selected		If Target.Cells.Count > 1 Then GoTo SelectionCode			If WorksheetFunction.CountA(Target.EntireRow) <> 0 Then				Me.UsedRange.Sort Key1:=[A2], Order1:=xlAscending, _					Header:=xlYes, OrderCustom:=1, MatchCase:=False, _					Orientation:=xlTopToBottom		End If	Application.EnableEvents = TrueExit Sub 'Our code will only enter here if the selection is _more than one cell.SelectionCode:	If WorksheetFunction.CountA(Selection.EntireRow) = 0 Then		Me.UsedRange.Sort Key1:=[A2], Order1:=xlAscending, _			Header:=xlYes, OrderCustom:=1, MatchCase:=False, _			Orientation:=xlTopToBottom	End If	Application.EnableEvents = TrueEnd Sub

To use either one of the above codes:

Open Excel.
Right click on the Sheet name tab.
Select View Code from the Pop-up menu
Copy the code and paste it over the top of the default Event
Push Alt+Q to return to Excels normal view.
Push Alt+F8 and then select the macro name and click Run. Or select Options and assign a shortcut key.

Of all the examples above that use Excels AutoFilters and Sort are by far the quickest methods I know of. If anybody knows of a quicker way, please tell me.

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALLpurchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Try out:Analyzer XL |Downloader XL |Smart VBA |Trader XL Pro (best value) |ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader PackageTechnical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft