Deleting empty worksheet rows
Deleting empty worksheet rows
Tip I refer to Gabriel Schervish Belmont's question Delete all empty rows in a flash (November 97, p178). I need to do this regularly and was very interested in the answer provided. However, I have used an alternative method which does not involve writing a macro and which may be just as easy -- at least if the number of rows is not too great. My method uses Excel's AutoFilter function. Assume that you have text/data in Columns A to C in, say, Rows 1 to 25, with blank rows (ie rows with blank cells in Columns A, B and C) at Rows 4, 15, 16 and 22. Assume also that the following cells are blank: B9, C9, C19 and A20. First we must create a data list from the data in the range A1 to C25. This is done by clicking any cell in the data list and selecting Data--Filter--AutoFilter. This, however, will only recognise data up to and down to the nearest blank rows. To enable AutoFilter to look beyond the first blank line above and the first blank line below the selected cell, and to recognise our full range of data as the data list, temporarily fill Column D (Rows 1 to 25) with any data, eg x or 1.  Add temporary data to Column D before applying Excel's AutoFilter  AutoFilter will isolate all rows containing blank cells Now, when the AutoFilter is applied, drop-down filter boxes will appear in the first row of Columns A to D. In each column (A, B and C), filter on (Blanks). This will filter out all non-blank rows, leaving only the blank rows 4, 15, 16 and 22 visible. Select these rows, right-click and choose Delete Row. Excel will only delete the visible (blank) rows. Now select (All) in the drop-down filter boxes to reveal all the non-blank rows in the original order. If required, you can now delete the temporary "link" data in Column D and remove the AutoFilter. - Chris Morley | Category: Spreadsheet Issue: Mar 1998 Pages: 147 |
These Web pages are produced by Australian PC World © 1997 IDG Communications