by Luis Portell » Sun, 12 Sep 1999 04:00:00
by Harlan Gro » Sun, 12 Sep 1999 04:00:00
by Luis Portell » Mon, 13 Sep 1999 04:00:00
>>In excel 97 I would like to write a macro that will go through the entire
>>worksheet and test each row to see that if the record is blank or has an
>asterisk
>>that record will be deleted. The worksheet is usually over 100 rows long and
>>actually there are over 50 worksheets to do every week.
>Easy & fairly quick (credit to Bernie Deitrick): create a deletion key in
>another column, basically the row number if the row should be preserved or 0 if
>it should be deleted, sort the entire worksheet's used range on the deletion
>key in ascending order, then delete the rows with deletion key = 0.
by Harlan Gro » Mon, 13 Sep 1999 04:00:00
>>>In excel 97 I would like to write a macro that will go through the entire
>>>worksheet and test each row to see that if the record is blank or has an
>>>asterisk that record will be deleted. The worksheet is usually over 100
>>>rows long and actually there are over 50 worksheets to do every week.
>>Easy & fairly quick (credit to Bernie Deitrick): create a deletion key in
>>another column, basically the row number if the row should be preserved
>>or 0 if it should be deleted, sort the entire worksheet's used range on the
>>deletion key in ascending order, then delete the rows with deletion key = 0.
> =======
>FINE BUT HOW DO I GET EXCEL TO DO THIS, NAMELY TO PLACE THE ROW
>NUMBER IF THE ROW IS TO BE PRESERVED OF 0 IF IT SHOULD BE
>DELETED. I REALIZE I COULD DO THIS MANUALLY BUT THAT IS PRECISELY
>WHAT I AM TRYING TO AVOID
=IF(AND(MIN(LEN(A2:F2))>0,ISERROR(FIND("*",A2:F2))),ROW(),0)
I.e., if no blank cells and no asterisks then keep the record -> show row
number; otherwise, delete the record -> show 0. Sorting based on this key
should work, but you could copy the range of keys and Paste-Special-Value on
top of this range to be absolutely sure there aren't any sorting anomalies due
to sorting on a formula.
by Luis Portell » Mon, 13 Sep 1999 04:00:00
>>>>In excel 97 I would like to write a macro that will go through the entire
>>>>worksheet and test each row to see that if the record is blank or has an
>>>>asterisk that record will be deleted. The worksheet is usually over 100
>>>>rows long and actually there are over 50 worksheets to do every week.
>>>Easy & fairly quick (credit to Bernie Deitrick): create a deletion key in
>>>another column, basically the row number if the row should be preserved
>>>or 0 if it should be deleted, sort the entire worksheet's used range on the
>>>deletion key in ascending order, then delete the rows with deletion key = 0.
>> =======
>>FINE BUT HOW DO I GET EXCEL TO DO THIS, NAMELY TO PLACE THE ROW
>>NUMBER IF THE ROW IS TO BE PRESERVED OF 0 IF IT SHOULD BE
>>DELETED. I REALIZE I COULD DO THIS MANUALLY BUT THAT IS PRECISELY
>>WHAT I AM TRYING TO AVOID
>No need to shout.
I'm assuming you're not checking each and every column on on
...........yes one or more than one should trigger deletionQuote:>each row, just certain columns, to see if they're 'blank' or 'has an asterisk'
>(one or more than one both trigger deletion?).
The generic test for a
.............good lesson for a novice like me. I have used excel for years but only now will IQuote:>'nonblank' cell is =LEN(cell)>0 . The generic test for 'no asterisks' is
>=ISERROR(FIND("*",cell)) . Now let's say you're testing columns A through F.
>The complete formula for the key for the record in row 2 would be the array
>formula (you can look up array formulas in online help if you need to)
>=IF(AND(MIN(LEN(A2:F2))>0,ISERROR(FIND("*",A2:F2))),ROW(),0)
.............Quote:>I.e., if no blank cells and no asterisks then keep the record -> show row
>number; otherwise, delete the record -> show 0. Sorting based on this key
>should work, but you could copy the range of keys and Paste-Special-Value on
>top of this range to be absolutely sure there aren't any sorting anomalies due
>to sorting on a formula.
1. Macro to delete certain rows
2. (asp) paging thru records 10 @ a time....
3. VBA code for Iif Function to delete rows meeting certain criteria
4. Clients mysteriously installing / deinstlling themseleves.
5. deleting row in datagrid ... and call a selected record in another Form from dataGrid
6. Inertial Guidance for Dummies
7. Trouble with writing a macro to delete duplicated rows
8. ** Macro Help to delete rows **
9. delete a empty row - Macro - excel?
11. Excel macro needed for deleting the old rows.