VBA If statement tip

When creating long VBA code it is common that the start of an If statement and the matching End If statement may not be visible on the same screen.

When scrolling around trying to understand your code it can be useful to include the If statement itself as a comment following on the same line as the End If command – see examples below.

If x=0 Then
 
  'lots of code
 
  If y =1 Then
 
    'lots of code
 
  End If   'If y =1 then
 
End If   'If x=0 Then

The apostrophe is used to specify the start of a comment – you can have a comment following a line of code.

This structure can assist when trying to identify which End If statement relates to which If statement.

Entering Date and Time in Excel

There are shortcuts to enter

  • the current Date Ctr + ;
  • the current Time Ctrl + Shift + :

There isn’t one to enter both.

You can use them in sequence to achieve a date and a time entry.

In sequence press

Ctrl + ;

Space bar

Ctrl + Shift + :

The space separates the date and time and Excel will recognise the entry as a date and time.

Get & Transformation

Power Query morphed into Get & Transform and now in the latest Excel 2016 subscription release Get & Transform has expanded on the Data Ribbon.

Creating a table of all combinations in Excel [Video]

Power Query solution

Let’s assume you have three state codes and four department codes and you want to create a table of all the possible 12 combinations (3 x 4). How do you do it so that it is flexible? i.e. if you add a new state or department it must be easy to update the combination table.

Filtering blanks made easy

In Excel the “Blanks” option is usually at the bottom of the list. This slows down selecting it.

If you have a lot of entries you need to scroll all the way down to bottom of the list to choose it – see image below.

But the word “Blanks” is searchable, so if you type b in the Search box – your work is done – no scrolling required – see image below.

If your column contains text you might need to type in bla.