Solved: paste value for filtered data

[danovkos]

Hi all,
please can you help me?
How can i change this code, that it can be applied also for filtered data. I mean, when is filter applied and i select data and want to change them to values.
Now it works only for non filtered data. If i use filter and want use this code it return error:

"The information cannot be pasted because the copy area and the paste area are not the same size and shape. Try one of the following....


Sub paste_value()
On Error GoTo Excel:
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
DisplayAsIcon:=False
Excel:
If Application.CutCopyMode = xlCopy Then
Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

ElseIf Application.CutCopyMode = False Then
On Error GoTo 0
End If
End Sub
thx for your help

[p45cal]

Simplest is after you've selected your filtered list execute this statement:
selection=selection.value
This will convert formulae to values just for the visible cells in the filtered list.
Is it worth calling a separate paste_special sub to do this?

[danovkos]

i dont know why, but this code change the values :(

[p45cal]

Correction! This doesn't work. Looking further..

[p45cal]

Interim solution: This code converts the visible cells of the filtered list on the ActiveSheet to values: Sub blah()
Set AFrng = ActiveSheet.AutoFilter.Range
For Each cll In AFrng.Offset(1).Resize(AFrng.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Cells
cll.Value = cll.Value
Next cll
End Sub
and a bit faster:Sub blah()
Set AFrng = ActiveSheet.AutoFilter.Range
For Each rw In AFrng.Offset(1).Resize(AFrng.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Rows
rw.Value = rw.Value
Next rw
End Sub

[danovkos]

yes this works great...thank you very much.
and is there a possibility, that it will works the same code for data in autofilter and also for data without autofilter
...with any condition, if is applied autofilter...then...

[p45cal]

It will only work on the Autofilter of the active sheet (as it's written). If that autofilter is not actually filtering anything (ShowAll) it will still convert the visible cells in that autofilter's range.
Is this what you want, or do you want nothing to happen if it's showing all?
And if this latter is the case, if there is a filter in place but it doesn't hide any rows at all, what do you want to happen then?

[danovkos]

i asked because i want to use the same code for more cases.
the 1. is this - autofilter is applied...
2. for other changing formulas to values...and in those cases can be, that there in no autofilter applied

never mind...thank you for your help

PS. can i one short question?
how can i select whole columns of selected area?
i have this:

Columns(ActiveCell.Column).Select
but this select only active cell..but if i have selected area A1:F10 and want select whole A,B,C,D,E,F columns..how can i define this (the columns can be other - not always A,B...F?
thx a lot

[p45cal]

selection.entirecolumn.select

or

range("$G$4,$J$24,$M$6").entirecolumn.select

[danovkos]

GREAT :):clap: :bow:
THANK you very much