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:

Sub test()
Dim oneCell As Range
With ThisWorkbook.Sheets("sheet1").Range("T:T")
    For Each oneCell In Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
        If oneCell.Value = "yes" Then
            oneCell.EntireRow.SpecialCells(xlCellTypeConstants).ClearContents
        End If
    Next oneCell
End With
End Sub

My code to sort:

Range("B2").Select
        Range(Selection, Selection.End(xlDown)).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Sort Key1:=Range("F2"), Order1:=xlAscending, Key2:=Range("E2"), _
            Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
            False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
            :=xlSortNormal
        Range("B3").Select

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:

SLNO	NAME	ADD	QTY	RATE	TOTAL	OUT?
1	NAME1	ADD1	15	7	105	
2	NAME2	ADD2	22	4	88	YES
3	NAME3	ADD3	18	5	90	
4	NAME4	ADD4	19	9	171	YES
5	NAME5	ADD5	14	7	98

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.