Dear All,

I am in filtered mode and a column cells contains formulae.
while remain in filtered mode and copying all those filtered cell contains
formulae and pasting
as value the following error encountered.
The information can not be pasted becoz copying area and paste area not the
same size and shape.
Try one of the following:

click on a single celland then paste.
Its to difficult to copy each cell and pasting as value becoz no of cell is
more than thousands.

Please can i have any option to overcome this problem ?????


Please assist ?

Rgds,

Aligahk06

Maybe you sort your data so that the visible rows are grouped together after
you've applied the filter.

Then copy|paste special|values and sort your data back to the way you like.

======
Alternatively, you could use a macro that looks at each area one at a time.

If you want to try:

Option Explicit
Sub testme()

Dim myVisRng As Range
Dim myVisFormulaRng As Range
Dim myArea As Range

Set myVisRng = Nothing
On Error Resume Next
Set myVisRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeVisible))
On Error GoTo 0

If myVisRng Is Nothing Then
MsgBox "No visible cells in selection!"
Exit Sub
End If

Set myVisFormulaRng = Nothing
On Error Resume Next
Set myVisFormulaRng = Intersect(myVisRng, _
myVisRng.Cells.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0

If myVisFormulaRng Is Nothing Then
MsgBox "no formulas in visible range"
Exit Sub
End If

For Each myArea In myVisFormulaRng.Areas
myArea.Value = myArea.Value
Next myArea

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

==========
You'd apply the filter, select the range to convert (you can include the visible
and hidden rows since the macro looks for them).

Then run the macro.

Aligahk06 wrote:

Dear All,

I am in filtered mode and a column cells contains formulae.
while remain in filtered mode and copying all those filtered cell contains
formulae and pasting
as value the following error encountered.
The information can not be pasted becoz copying area and paste area not the
same size and shape.
Try one of the following:

click on a single celland then paste.
Its to difficult to copy each cell and pasting as value becoz no of cell is
more than thousands.

Please can i have any option to overcome this problem ?????

Please assist ?

Rgds,

Aligahk06

--

Dave Peterson