Wednesday, February 22, 2012

VBA: Deleting Rows Programmatically

When you need to delete rows in your spreadsheet programmatically, start from the last row and head towards the first one. This can be accomplished by using Step -1 in the For loop, see example below:

For lRow = lLastRow To lFirstRow Step -1
Next lRow

If we would loop starting from the first row, our lRow counter would be pointing incorrectly at the moment when a first row gets deleted.

Friday, February 10, 2012

Excel Shortcut: Select Entire Worksheet / Select All Data

If worksheet contains data, Ctrl + A selects the current region. To select entire worksheet press Ctrl + A a second time.

Excel Shortcut: Select Entire Row

To select the entire row in Excel, select cell in that row and press Shift + Space

Excel Shortcut: Select Entire Column

To select the entire column in Excel, select cell in that column and press Ctrl + Space

Excel Shortcut: Open VBA Editor

To quickly open VBA Editor window press Alt + F11

Excel Shortcut: Insert Comment or Edit Existing

To insert a new comment or to edit an existing one, select the cell and press Shift + F2

Quickly Copy Outlook Appointment's Tracking Status to Any Other Program

Scheduled an appointment in Outlook and want to quickly get the attendees' tracking status to say - Meeting Minutes or notes?

In Outlook 2010 this is easy:
  • Open the appointment in Outlook
  • In the Meeting ribbon click the small arrow under Tracking button
  • Click "Copy Status to Clipboard"
  • Open Word/Excel/other and paste - you're done!
How to copy Outlook appointment tracking status

The format is pure text with tab separated values so you can paste it to almost any program.