Aug 24, 2012

Remove pictures from copy/paste into Excel

I don't know about the rest of you, but I constantly find myself copying & pasting information from websites into Excel spreadsheets so that I can manipulate the data, or to just keep track of it for future reference.

I recently did just this when creating backup copies of my different domain's DNS entries.  The problem is, when I paste the tabulated data into Excel, it includes hyperlinks & images.

Hyperlinks:  Luckily in the newer versions of Excel this is as easy as selecting the whole sheet, right-clicking and selecting "Remove Hyperlinks".

Images:  Images are a little more difficult, since they often times don't even exist inside a cell, so they can't be easily deleted.  Thanks to Dave Peterson over on the microsoft.public.excel newsgroup, I got this little nugget of information:

Hit Alt-F11 to get to the vbe (Visual Basic Editor)
Hit Ctrl-G  to see the "immediate" window
Type the following and then press enter:
Activesheet.pictures.delete
This blows them all away quickly & cleanly.  What's even better, is that if you're doing this multiple times, just keep copying & pasting pages in, then with each sheet highlighted, alt-tab back to the VBE screen, put your cursor at the end of the line & hit enter again to repeat the process.