Deleting Rows

A commonly asked question is, "What is the best way to delete unneeded rows from a spreadsheet?" Generally, the requirement is to find the rows that have certain text in a given column and remove those rows. The best solution depends on how large the spreadsheet is and how many items are likely to be removed.

Say that you want to remove all the rows that contain the text Mangoes in column C. One way to do this is to loop through all the rows and test every cell in column C. If you do this, it is better to test the last row first and work up the worksheet row by row. This is more efficient because Excel does not have to move any rows up that would later be deleted, which would not be the case if you worked from the top down. Also, if you work from the top down, you can't use a simple For...Next loop counter to keep track of the row you are on, because as you delete rows, the counter and the row numbers no longer correspond:

Sub

DeleteRows()

Dim lRow As Long

'Freeze screen

Application.ScreenUpdating = False

'Process rows from last data row up to row 1

For lRow = Cells(Rows.Count, "C").End(xlUp).Row To 1 Step -1

'Delete rows with Mangoes in C column

If Cells(lRow, "C").Value = "Mangoes" Then

Cells(lRow, "C").EntireRow.Delete

End If

Next lRow

End

Sub

A good programming principle to follow is this: If there is an Excel spreadsheet technique you can utilize, it is likely to be more efficient than a VBA emulation of the same technique, such as the For...Next loop used here.

Excel VBA programmers, especially when they do not have a strong background in the user interface features of Excel, often fall into the trap of writing VBA code to perform tasks that Excel can handle already. For example, you can write a VBA procedure to work through a sorted list of items, inserting rows with subtotals. You can also use VBA to execute the Subtotal method of the Range object. The second method is much easier to code, and it executes in a fraction of the time taken by the looping procedure.

It is much better to use VBA to harness the power built into Excel than to reinvent existing Excel functionality.

However, it isn't always obvious which Excel technique is the best one to employ. A fairly obvious Excel contender to locate the cells to be deleted, without having to examine every row using VBA code, is the Edit OFind command. The following code uses the Find method to reduce the number of cycles spent in VBA loops:

Sub

DeleteRows2()

Dim rngFoundCell As Range

'Freeze screen

Application.ScreenUpdating = False

'Find a cell containing Mangoes

Set rngFoundCell = Range("C:C").Find(What:="Mangoes")

'Keep looping until no more cells found

Do Until rngFoundCell Is Nothing

'Delete found cell row

rngFoundCell.EntireRow.Delete

'Find next

Set rngFoundCell = Range("C:C").FindNext

Loop

End

Sub

This code is faster than the first procedure when there are not many rows to be deleted. As the percentage increases, the code becomes less efficient. Perhaps you need to look for a better Excel technique.

The fastest way to delete rows that I am aware of is provided by Excel's AutoFilter feature:

Sub DeleteRows3()

Dim lLastRow As Long 'Last row

Dim rng As Range

Dim rngDelete As Range

'Freeze screen

Application.ScreenUpdating = False

'Insert dummy row for dummy field name Rows(1).Insert

'Insert dummy field name Range("C1").Value = "Temp"

With ActiveSheet

'Reset Last Cell

.UsedRange

'Determine last row lLastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row

'Set rng to the C column data rows

Set rng = Range("C1", Cells(lLastRow, "C"))

'Filter the C column to show only the data to be deleted rng.AutoFilter Field:=1, Criteria1:="Mangoes"

'Get reference to the visible cells, including dummy field name Set rngDelete = rng.SpecialCells(xlCellTypeVisible)

'Turn off AutoFilter rng.AutoFilter

'Delete rows rngDelete.EntireRow.Delete

'Reset the last cell .UsedRange

End End Sub

With

This is a bit more difficult to code, but it is significantly faster than the other methods, no matter how many rows are to be deleted. To use AutoFilter, you need to have field names at the top of your data. A dummy row is first inserted above the data, and a dummy field name is supplied for column C. The AutoFilter is only carried out on column C, which hides all the rows except those that have the text Mangoes.

The SpecialCells method is used to select only the visible cells in column C, which includes the dummy field name row. A reference to these rows is assigned to rngDelete. The AutoFilter is turned off and the rows in rngDelete are deleted.

+3 0

Responses

Post a comment