change formulas to

[danovkos]

hi,
pls. is it possible to made code, which will change range of formulas of filtered data to values?

I try to avoid this error when i try to change formulas to values in selected filtered data.

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....

But area is the same but is filtered.

What i mean is.
I have data about 6000 rows and 100 col. I use existing filter (e.g. amount 1000 in col D) and then i select part of filtered data. This area contains formulas. Then want to run code and i to change this selected, filtered formulas to values.

How can i do this?
thx for your help.

[lucas]

You can do this with vba by using xlCellTypeVisible to select the cells to copy after filtering

[danovkos]

thx for advise.
i have this:


Sub paste_value2()
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


but if i use it for huge table (6000x100) it is very slow.
Is any possibility to optimize this that it works faster?
thx

[Bob Phillips]

turn off screenupdating, restrict the area to the actual columns not the whole rows

[danovkos]

is this right?



Sub paste_value2()
Application.ScreenUpdating = False
Set AFrng = ActiveSheet.AutoFilter.Range

For Each cell In AFrng.Offset(1).Resize(AFrng.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Cells
cell.Value = cell.Value
Next cell

Application.ScreenUpdating = True
End Sub


because i tried this but it take also long time...
thx

[Bob Phillips]

I guess 600,000 cells is just going to take a long time on your machine, unfortunately.

[danovkos]

ok, but i select only e.g. 150 row x 15col
it not depend on this?
this code doesnt works with whole table or?

[mdmackillop]

Try a row at a time

Sub paste_value2()
Application.ScreenUpdating = False
Dim AFrng As Range, Cell As Range
Set AFrng = ActiveSheet.AutoFilter.Range

For Each Cell In AFrng.Offset(1).Resize(AFrng.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Cells
Cell.EntireRow.Copy
Cell.EntireRow.PasteSpecial Paste:=xlValues
Next Cell

Application.ScreenUpdating = True
End Sub

[danovkos]

this code return error:
"paste special method of range class failed"
and stop in debug
on this line
Cell.EntireRow.PasteSpecial Paste:=xlValues