31 July, 2013

Delete all objects in Excel

I recently copied hundreds of rows of data from a webpage into Excel. Unfortunately it brought over several unwanted objects, which I needed to delete.

It seemed quite consuming if I was to select and delete each individually. Worse I discovered that I couldn't select some of the objects (checkboxes). I learned two new techniques for mass deleting objects.

If you can select the objects

  1. Press F5 -- this opens the "Go To" dialog box
  2. Click Special... -- this opens the "Go To Special" dialog box
  3. Select the Objects radio button
  4. Click OK -- this closes the dialog box and all the objects in the worksheet are selected
  5. Press Delete and all the objects are deleted
    • If you don't want all the objects deleted, first un-select those you don't want to lose.
    • If you get a message "no objects found", then move on to the next method.

For those objects that can't be selected

For those that know how to create a macro, here's the macro:
Sub foo()
Dim obj As Object
For Each obj In ActiveSheet.Shapes
      obj.Delete
Next
End Sub
For the rest of us who need a reminder...

  1. Press Alt-F8 to display the Macro dialog box
  2. Enter a new macro name (such as "foo") in the text box just below "Macro name:"
    - This will change the Create button from an inactive to active state
    - If you already have one or more macro, the name of one may already be in the "Macro name:" text box. That's ok, just type over it and Excel will realize you're wanting to create a new macro.
  3. Click Create -- this opens the Visual Basic Editor
  4. Copy the lines above and paste into the edit window
    - Note if you didn't use "foo" as a name, you will need to make sure you update the macro code accordingly
  5. Close the editor window -- you should be back on your worksheet
  6. Press Alt-F8 again
  7. Select your new macro and click Run -- all your objects should now be deleted from your worksheet
That's all there is too it! It took me longer to write this than it'll take you to read and use. ;)

26 July, 2013

RoboForm Extension not working in Chrome

RoboForm is one of those go-to programs that if it doesn't work in my browser, I don't use the browser. I became quite frustrated when RoboForm quit working in Chrome. I tried going into the Extensions manager to get any clues, and when I changed settings I got a pop up error message.

As it turned out, Chrome has a Windows 8 mode and a Desktop mode, and I was in Windows 8 mode. Apparently some Extensions in Chrome don't work in Windows 8 mode. I switched to Desktop mode, and magically RoboForm worked.