Deleting Rows

Topic

New Reply

Is it possible to delete more than one row at a time?

As an example. Range(“A5:A25000”) has many cells which contain a dash (-). Instead of testing the contents of each cell and deleting the respective row, I thought perhaps there was a way to determine which cells contain the dash and delete all those cells at one time.

Thanks,
John

Reply | Quote

Replies

Using a macro, you would still have to loop through all the rows to check for the “-“. It’s just as easy, to delete the rows as you go.

Reply | Quote

A non macro method (I’m using 2003 – Due to limitations of the Find routine, this method will not work in earlier versions):

Select the range you want to affect
Ctrl F to launch Find dialog
enter the data to find, check the match entire cell option
Click Find All
Highlight the results that appear
Click Close
Ctrl+”-” (launches delete dialog) choose Entire Row
OK out

I tried using the macro recorder, but the recorder did not give any useful information.

Reply | Quote

A non macro method (I’m using 2003 – Due to limitations of the Find routine, this method will not work in earlier versions):

Select the range you want to affect
Ctrl F to launch Find dialog
enter the data to find, check the match entire cell option
Click Find All
Highlight the results that appear
Click Close
Ctrl+”-” (launches delete dialog) choose Entire Row
OK out

You go me to think this out a little more. If one could pass the cell addresses to an array and then delete the each row of the array that may work.

If you select a number of cells by using the Ctrl Key and left click; one can delete all rows at the same time.

Thanks for taking another look,
John

Reply | Quote

In order to pass the row numbers to an array, you would still have to loop through all rows – why not delete them then? Using the Find method I described is like Ctrl+Clicking on the individual cells.

Reply | Quote

Why not add an autofilter. Filter to show the appropriate rows, then select the first row, and while holding SHIFT select the last row. When all are selected, you can delete rows to remove them and then turn off the filter…

Steve

Reply | Quote