Hi all thanx in advance
I created a macro to create a new sheet and clone cells from an older sheet
like this
='Sheet1'!B1
then the macro does autofill until row 2000 the reason of doing that is
because sheet1 for example is designed to hold info until that no. of cells
and I want to sort only those columns copied to that sheet, but the problem
is when doing sort with autofilter on, all the empty cells which contains a
value but is not used because on the older sheet this cell is empty (but on
this sheet it shows a zero 0) goes to the top,

My question is how do I delete all the unused cells with the formulas?

Please respond as quickly as possible Thanx!

please respond as quicly sa possible Thanx

The following code will clear the content of a cell that contains a
formula and whose result is zero. MAKE A BACKUP COPY of your data
before you use this, so you can be sure it does what you want it to do!

Sub Scrub()
Dim nCell As Variant

For Each nCell In ActiveSheet.UsedRange
If nCell.Value < "" And Mid(nCell.Formula, 1, 1) = "=" And _
nCell.Value = 0 Then nCell.ClearContents
Next nCell

End Sub

Dave's solution is removing the formulas with zero values as if you
would never use those formulas again regardless of changes made to
the cells that they had been dependent on, so I think it is an unacceptable solution.
You could do your magic on a copy of the worksheet.

You can change your settings in Tools, options, view, uncheck zero values
which is not clear but it will apply only to the one worksheet. In any case
it would apply to all cells on the worksheet and regardless of whether they
are formulas or number constants.

To be selective you could use conditional formatting and white out
the font. You can still see the zeros in the display if you select all cells;
otherwise you won't see them on a white or automatic (white) background.
They will not print; however, they will print if you have turned on the
Print B&W under File, Page Setup, Sheet. I think C.F. is your safest
solution as you can pick which columns and/or cells are to be affected.
If you have other C.F. then you are, of course, affected by the 3 C.F.
formulas per cell and the their order
Select column D, format, conditional formatting, cell value is equal to 0
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Dave O" wrote in message oups.com...
The following code will clear the content of a cell that contains a
formula and whose result is zero. MAKE A BACKUP COPY of your data
before you use this, so you can be sure it does what you want it to do!

Sub Scrub()
Dim nCell As Variant

For Each nCell In ActiveSheet.UsedRange
If nCell.Value < "" And Mid(nCell.Formula, 1, 1) = "=" And _
nCell.Value = 0 Then nCell.ClearContents
Next nCell

End Sub

Actually a much older solution is better for suppressing zero values
selectively -- format with a custom format so that the third parameter
is empty which is a zero value. This allows you to be selective.
Format, cells, number
#,###.00;-#,###.00;;@
the fomat of custom formatting is
positive numbers; negative numbers; zero; text
you can override positive, negative but those are the defaults.

see the topic "Create a custom number format" in your Excel Help.

related: http://www.mvps.org/dmcritchie/excel/formula.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm