How can I Convert Links into Values for only Visible Cells?

[Murtaza]

How can i convert links into values for only visible cells for selected
range(s)?

Regards,
Murtaza

 

[Dave Hawley]

Hi Murtaza

Push F5 then click Special, then "Visible cells" then ok. Now do copy -
paste special- values

*****Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum & Business Software*****

 

[Murtaza]

Thanks for your reply but
I got error "That command cannot be used on multiple selections."

Thing is that I have a series of rows, say from A1-A5 (visible & links) then
A6 (hide & formula). Now again from A8-A13(visible & links) and A14 (hide &
formula).

So i just want to convert links to values. formulas remain unchange.

 

[Dave Hawley]

Try this macro

Sub MakeValues()
Dim rRange As Range
Dim iArea As Integer
On Error Resume Next
Set rRange = Selection.SpecialCells(xlCellTypeVisible)
For iArea = 1 To rRange.Areas.Count
rRange.Areas(iArea) = rRange.Areas(iArea).Value
Next
End Sub

To use it push Alt+F11 then Insert>Module and paste in the code. Now
click the top rigt X to get back to Excel. Select your range, then push
Alt+F8, select "MakeValues" and click Run.

*****Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum & Business Software*****

 

[Murtaza]

Thansk Dave!
It really saves my day

Murtaza