Delete Entire Row, but keeping the Formula in tact

Dear Friends,

I have a worksheet with cols A2:AP55. In this, I already defined three Allow users Ranges ((Range1 - A:P), (Range2 - R:AC), (Range3 - AE:AP)). ColQ, ColAD consist of Formulas and it shows the values based on the previous columns respectively.

When I need to Clearcontents (I DONT WANT TO DELETE ROW) a row based on a value in a column, it removes the formula also.

For Example, my code to find "YES" in colT and if it is there it should clear contents (of entire row) only the values keeping the formulas in tact.

As always, Any help is very highly appreciated.

Thanks in advance.

acsishere.

Thanks for your code. It works nicely. But I tried to incorporate it into my workbook, but in vain.

Actually what I want is:
A2:AP55 my data is there. In between ColT will accept only one value "YES". If the macro is executed, then it should find "YES" in colT and Clearcontents, AND THEN, it should sort the data by 2 levels (i.e. First by ColF and and then by ColE).

Your code is:

Please Login or Register  to view this content.

My code to sort:

Please Login or Register  to view this content.

Can you please tell me where I am to modify? Or entirely the code should be modified?

Please Sir,

acsishere.

Best wishes!!

I think I haven't clearly explained in my previous post. The clear picture is here:

I have a worksheet with cols A2:AP55. In this, I already defined three Allow users Ranges ((Range1 - A:P), (Range2 - R:AC), (Range3 - AE:AP)) and protected the sheet. So that the formula & data validations are protected. ColQ, ColAD consist of Formulas and it shows the values based on the previous columns respectively.

When I need to Clearcontents (I DONT WANT TO DELETE ROW) a row based on a value in a column, it removes the formula also. Thanks for Mr. Mikerickson for his code to clear only the contents keeping the formulas in tact.

Example:

Please Login or Register  to view this content.

In the above example, the TOTAL is a calculated column (QTY * RATE). Hence, for data entry purpose, I am to allow the users only to enter the values in blank cells. And other areas are protected.

In this case, the contents of Slnos. 2 & 4 are to be removed (using ClearContents & without disturbing formulas). And then the data should be sorted first by ADD and then by NAME columns.

As always, Any help is very highly appreciated.

Thanks in advance.

acsishere.

Mr. Mikerickson has given the partial soution to clear contents of the rows without disturbing the formulas. Thanks to Him.

I just need to bring all rows one-by-one (without blanks). Always the rows should start from Third row (1st & 2nd rows are header rows).

And hence, the macro is required to find rows with values (from A3 to to last row used) and bring them up together (when it brings-up the rows should not be deleted. Instead, it should copy only the constant values and pastespecial - values (using SpecialCells(xlCellTypeConstants)).
So that all rows are arranged without any blank rows.

Thanks a lot, in advance.

acsishere.

Thanks for your reply.

I attached the sample workbook for your kind ref.

Thanks a lot.

acsishere.

The code works nicely in unprotected worksheet.

When I applied it into my workbook (the activesheet is protected with a password "PASSWORD") module it does nothing.

My workbook is with:
1) First 2 rows are Header rows
2) As the rows and columns may increase, it may require xlup & xldown selections.
3) All cells are locked & hidden.
4) sheet protected with the password "PASSWORD".
5) The search for "Y" column is col Y:Y

I changed the code as follows:

Please Login or Register  to view this content.

I don't know why it is not working in my sheet.

Your help is highly appreciated.

acsishere.