Kev's Development Toolbox

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

Conditionals:

If condition Then
  'do stuff
End If

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

Iteration:

For value To upperValue
    ' Do stuff
Next

While condition
  'do stuff
Wend

Logic Operators

= equal

<> not equal

And

Or

Not

Example:

If somevalue = othervalue Then
    'do stuff
End If

Line continuation use _ at end of line

String Concatenation: string1 & string2

Subroutines:

Sub name()
  'do stuff
End Sub

Functions

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

Sheets("SheetName").UsedRange.Rows.Count

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

isError(cellreference)

 

Clearing sheet ranges

Sheets("sheetname").Range("A1:B1000").ClearContents

 

Building a Range with concatenated values from Strings

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

 

Copying a range from one sheet to another

Sheets("sheet1").Select
Range("A2:B100").Select
Selection.Copy
Sheets("sheet2").Select
Range("A2").Select
ActiveSheet.Paste

 

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

Trim(somevalue)

 

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:

=INDEX(G$1:M$1,MATCH(LARGE(G2:M2,1),G2:M2,FALSE))

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