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

Installing OS/2 Warp on VirtualBox

Why? My first job after graduating college was working as a contractor for IBM UK in the software support centre (Basingstoke, UK) supporting OS/2 and various other apps (Comms Manager/2, DB2/2, CICS OS/2). For a number of years I think I had a stack of OS/2 install diskettes in a box somewhere, but they got thrown out as trash at some point.

A few points over the years I looked to see if once the product was discontinued if there would be a free download of the install disk images. Well, IBM officially discontinued the product in 2005 and support ended in 2006. I can’t remember what I was looking for when I run into this, but over on the Internet Archive site they now have disk image archives for most of the major versions – the OS/2 Warp 4 download disk images are here (other versions, Warp 3, 2.1, 2.0, 1.x are also in the archive).

Turns out Warp 3 installs pretty easy on Virtual Box: here’s a couple of screenshots of the installation:

OS/2 Warp 3 installation

OS/2 Warp 3 installation 2

The disk images for Warp 4 from the Internet Archive site didn’t work for me on Virtual Box. I read some posts this is because the disk image format (dsk?) for the install floppies isn’t a common/standard supported format.

I found another download that used the .img disk image format and this version installed just as easily:

OS/2 Warp 4 install

OS/2 Warp 4 install 2

So what now? Well, there’s a huge list of instructions here for installing the latest (and last) fixpacks, so maybe I’ll play around with this for a while…