Excel: formulae and VBA tips

By no means an expert, but here’s some Excel tips, useful to remember for future usage:

  • Jump to row: Ctrl G, then enter a cell reference, like C10000 – useful for jumping around large sheets
  • Jump to end: Ctrl End – jump to the last row with values (doesn’t work if you’ve pasted a repeating formula into a whole column though)
  • Sorting a column – if you have a header row, make sure every column has a value in the header row, otherwise the header row will get sorted too

VBA Useful Tips

Basic language constructs

Declaring variables

Dim variable name

Can declare vars as a type with As typename


If condition Then
  'do stuff
End If
If condition Then
 'do stuff
 'do stuff
 End If


For value To upperValue
    ' Do stuff
While condition
  'do stuff

Logic Operators

= equal

<> not equal





If somevalue = othervalue Then
    'do stuff
End If

Line continuation use _ at end of line

String Concatenation: string1 & string2


Sub name()
  'do stuff
End Sub


Function name As returntype
  'do stuff
  name = return value ' assigning the return value to the function name returns the value
End Function


Other Useful Stuff

Get number of populated rows in a sheet


If you have values inserted by a formula that are displayed as #N/A, you can test for this with:



Clearing sheet ranges



Building a Range with concatenated values from Strings

Range("A1:" & "B" & CStr(someStringValue)).Select


Copying a range from one sheet to another



Remove duplicate rows in a range

ActiveSheet.Range(Range("A2"), Range("C100")).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes


Copying a cell value to a variable

Dim somevalue
someValue= Sheets("sheet1").Cells(rownumber, "F").Value


Trimming spaces



VBA VLookup – using a declared range name on an existing sheet

lookupvalue= Application.VLookup(value_to_lookup, Range("a range name"), column_number_for_decode_value, False)


Function to lookup row contain last populated value

Function getLastPopulatedRow() As Long

Dim lastCellInColumn As Range
Set lastCellInColumn = Sheets("sheetname").Cells(Sheets("sheetname").Rows.Count, "A").End(xlUp)
getLastPopulatedRow= Application.WorksheetFunction.Max(lastCellInColumn.Row)

End Function


Inserting a formula into a cell and then copying it down a column

Sheets("sheetname").Cells(2, "D").Formula = "=SUM(O2:U2)"
Sheets("sheetname").Range("D2").Resize(lastRow - 1, 1).FillDown


 Using the value of one cell to lookup a value in another

I used this approach to find the highest value in a range of cells, and use that to return the heading label for that column:


In words, starting in the middle, this is finding the largest value (1, change this param for the nth largest) in range G2:M2, finding the relative position of the value in the range G2:M2, and then using that as an index to the range G1:M1 which contains the column headings – the value returned is the column heading that contains the largest value in the range G2:M2.


Disabling Excel Features During Long Running Macros and Formulae

If you’re dynamically writing content or updating content covering thousands of rows, sometimes Excel can slow down as it formulae are re-evaluating based on the dynamic updates. To work around this, there are features to can turn off:

Sub disableStuffForPerformance()

  Sheets("sheetname").EnableCalculation = False
  Application.ScreenUpdating = False
  'this setting makes the most difference increasing processing speed
  Application.Calculation = xlCalculationManual
  Application.EnableEvents = False

End Sub

Sub turnDisabledStuffBackOn()

  Sheets("sheetname").EnableCalculation = True
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
  Application.EnableEvents = True

End Sub

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.