copy paste

[afdmello]

In a coloumn which is in filter mode. I want to remove the formulas. I do
the copy and then apply the paste special > values I get the message the
paste area is not the same size as the copy area even though it is the same
column. The column has 30000 cells of data.
AFD

 

[Shane Devenshire]

Hi,

While your data if filtered Excel will not let you paste back on the range
because it sees the target range as including the hidden cells, while the
copy range included only visible cells.

Maybe the best solution is a VBA macro:

Sub Convert()
Dim cell As Range
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Selection.SpecialCells(xlCellTypeVisible).Select
For Each cell In Selection
cell = cell.Value
Next cell
Application.Calculation = xlCalculationAutomatic
End Sub

Cheers,
Shane Devenshire
Microsoft Excel MVP

 

[afdmello]

Thank you Shane. It means that I cannot perform the operation. I seldom use
excel and so I will do it manually rather than use the macro. Anyway I will
save your macro for a trial later.

When we hit the copy button we can see the double running lines indicating
that there are hidden rows.

Thank you for the time and effort.

AFD

 

[Gord Dibben]

After filtering, don't hit the copy button, run Shane's macro.

You will be pleased with the results.


Gord Dibben MS Excel MVP