Quickly delete every nth row in Excel
Thursday, October 22, 2009 12:07 PM
Susan Harkins explains how to use AutoFilter to delete specific rows in a worksheet.
Microsoft Excel
Quickly delete every nth row in an Excel worksheet
Most likely, you sometimes delete an empty row from a worksheet. And if you have to delete several rows, you might use a filter to automate the task. First, you determine which column best represents the entire row.
Next, you apply an AutoFilter that selects all the rows where that particular column is blank. Then, you delete the selected rows.
You can expand this simple technique to delete every nth row in the sheet, but it takes a bit more work. You'll need two new columns. In one column, use AutoFill to sequentially number the rows, starting with the number 1.
In the first row of the second column, enter the following formula:
=MOD(firstcellinsequence,n)
where firstcellinsequenceis a relative reference to the cell that contains the value 1 in the sequential number column, and n equals the nth value.
For example, in the figure below, the sequential number list is in column F. Therefore, the formula in G2 references F2 and returns 0 for every third row:
=MOD(F2,3)
Now you're ready to implement a filter to delete every third row in the sheet (A2:E11). First, select the rows where the MOD() function returns 0, as follows:
- Select the column with the Mod() functions. In this case, that’s G1:G11.
- Choose Filter from the Data menu.
- Select AutoFilter.
- Using the filter drop-down arrow, choose 0. Excel will display only the rows that contain a 0 in column G.
- Select the rows displayed by the filter; in this case, that's rows 4, 7, and 10.
- Choose Delete Row from the Edit menu to delete the selected rows.
- Remove the filter by selecting (All) from its drop-down list.
- Uncheck AutoFilter from the Filter menu (the Data menu).
- Delete the sequential number list in column F and the Mod() functions in column G. (Or leave them, if you need to repeat this task frequently.)
Deleting entire rows and columns has the potential to delete unseen data, so be careful. Before deleting a row or column, press [Ctrl]+[End] to find the last cell that contains a value or formatting in the current sheet.
Microsoft Access
Add a search control to an Access form
Populating an Access form is a simple task thanks to bound forms and controls. In fact, a wizard will walk you through the whole setup. The results, however, aren't always easily searchable. There are a number of ways to add a search feature, but a text box or a combo box in the header is one of the simplest to create and implement.
The first step isn't revamping the form though. The first step is determining which value you'll use as search criteria. A primary key value might be your first choice. Often, the primary key value isn’t practical. For example, users might want to search by a last name value, which may or may not be part of the data's primary key.
Once you know how users will want to search the data, you can create and implement a search solution. For example, the following instructions add a search control to the Orders form (in Northwind, the demo database that comes with Access):
- With the Orders form in Design view, open the header and insert an unbound text box control. You don't have to put the search control in the header, but doing so is a good way to offset the search feature from the rest of the form.
- Name the text box txtSearch.
- Open the form's module by clicking the Code button on the Form Design toolbar.
- Enter the following code for the txtSearch control's After Update event:
Private Sub txtSearch_AfterUpdate()
'Find record based on contents of txtSearch.
Dim strSearch As String
On Error GoTo errHandler
'Delimited for text search.
'strSearch = "OrderID = " & Chr(39) & Me!txtSearch.Value & Chr(39)
'Delimited for numeric values.
strSearch = "OrderID = " & Me!txtSearch.Value
'Find the record.
Me.RecordsetClone.FindFirst strSearch
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Sub
errHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & _
Err.Description
End Sub
With the form in Form view, enter an order number in the new search control and press Enter. Executing the control's After Update event searches the OrderID field for a match to the current value in txtSearch and updates the form and the subform, accordingly.
If there's no match, the form displays the first record in the recordset. Entering an invalid data type will generate an error. The error handling routine is rudimentary, so you'll want to test it thoroughly and include appropriate actions as necessary.
When applying this technique to your own forms, you should update two areas:
- Choose the appropriately delimited search statement and comment out the other. The code includes a sample statement, which is commented out, for searching a text field. This example searches a numeric field (OrderID in the Orders table).
- Substitute the explicit reference to OrderID with the appropriate field name. (See the highlighted statement above.)
There are many ways to implement a search feature, but this is one of the simplest. You'll find it works well as long as you're searching a single field. If the list of search values is relatively short, you can use a combo box instead of a text box and display the search values in the control's dropdown list for easy selection.
Microsoft Word
Override Word's automatic text selection
When selecting portions of text, Word tries to be helpful by predicting what you want to select. If you click in the middle of a word and then drag to the next word, Word will increase the selection to include all of the first word.
For example, if you click between the two l's in the word magically (below) and then slide into the next word, print, Word automatically extends the selection to consume all of magically, whether you meant to or not.
If you back-drag the selection, Word will undo the automatic selection, but that's a nuisance. To countermand Word's behavior for the current selection, hold down [Shift]+[Ctrl] before making your selection. Then, Word will limit the selection to only those characters you actually drag across.
You might find yourself overriding the automatic selection behavior more often than not. When this happens, consider disabling the feature as follows:
- From the Tools menu, choose Options.
- Click the Edit tab.
- Clear the When Selecting, Automatically Select Entire Word option.
- Click OK.
There are currently no comments for this post.