Commonly used Excel VBA snippets

Below is collection of frequently used Excel VBA snippets I have accumulated over the years. This page will grow overtime… Feel free to comment and share!

Loop through all sheets in the active workbook and print name

Sub PrintSheetNames()
  For i = 1 To Sheets.Count
    'Print name on each cell, or replace line below and insert new code to be executed on each sheet
    Cells(ActiveCell.Row + i - 1, ActiveCell.Column) = Sheets(i).Name 
  Next i
End Sub

Delete all shapes in a workbook

I use this subroutine because sometimes my workbook would be so cluttered by hundreds shapes that are not selectable using the Goto->Special window. Since deleting them one by one was impractical, I wrote this macro to cycle through the entire workbook for all sheets and delete every shape in each sheet.

Sub DeleteAllShapes()
  For i = 1 To Sheets.Count
    For Each S In Sheets(i).Shapes
      S.Delete
    Next S
  Next i
End Sub

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>