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.

Thanks

>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.

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.

>>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

THANK YOU FOR YOUR TIME

>>>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

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

'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)

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.

>>>>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)

start looking into macros and commands like the ones you are mentioning

.............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.

...............

Many thanks for your help

L. T. Portella

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.

5 post • Page:**1** of **1**