A few tidbits

Today, I’m just going to cover a couple of things that I’ve found really useful in the last couple of weeks. I’ve used most of these before, but I keep coming back to these as great ways to solve many problems.

Finding the last cell in a range

If you’re like me, and can never remember the code equivalent of shift + end, down in vba, so you record it, and then use that recorded snippit. Stop it. It’s terrible.

You’ve a few options when finding the last cell, but the easiest, is to go to the bottom of the spreadsheet, and go up till you hit a cell, ie. if you were recording it, go end,down a few times till you hit the ‘bottom’, then end, up.

If you record this, you’ll have to select, and then move from selection. This is slow. Better to use something similar, such as the following:

 lastCellRowNum = Sheets("Sheet1").Range("A65000").End(xlUp).Row 

this admittedly, is still a bit of a cheat – what if you have more than 65000 rows of data, or what if you only want the first block. what if there’s a field name or something wierd at the end of your data… well, you deal with it and look it up  😉

Use dictionaries

I’ve done a bit of programming in javascipt, and I love javascript objects, which are so easy to use compared to vba objects / collections / arrays.

Arrays: you always have to either know how big it is going to be before you start, or redim it.

Collections: what’s the key to finding things?

Dictionaries are the answer: They let you store a unique key, and assign values. I find using collections as values really helpful (and familiar to arrays in JS)

I’m not going to tell you how to do it. Go to this site, its brilliant: https://excelmacromastery.com/vba-dictionary/

Adding Hyperlinks in VBA

Adding hyperlinks using vba can make your spreadsheet easier to navigate for users, and heck, is some good ol’ time fun.

The key here is a) you must activate the sheet you’re about to add the hyperlink to using something like:

sheet("mySheet").activate

Then, you can add the link to somewhere inside your workbook using the following:

Sheets("mySheet").Hyperlinks.Add Anchor:= _
Sheets("mySheet").Range("A2"), _ 'this is where the hyperlink will go
Address:="", _ 'we don't need this for links inside the workbook.
SubAddress:="'OtherSheet'!A1", _ 'this can be variablised.
TextToDisplay:=mySheet.Range("C3").Value

I think any of the ranges in that code can be replaced by a variable referencing a range, but I haven’t tested it on subAddress.

Pasting a 1-D array into a range quickly

You have an array, you’ve built it that way, because handling arrays is super quick. But, you don’t want to write a for loop over the array to paste it into a range, because, yuck.

Instead:

Set destination = Sheets("MySheet").Range("A1")
Set destination = destination.Resize(1, UBound(myArray) + 1)
destination.Value = MyArray

The reason for the plus 1, is that the array starts at 0 (option 0), whereas you have a real cell you need to fill (if you forget, you’ll likely miss the last item in each array).

This obviously fills across columns. If you want to do rows instead, something like the following code could be used (combining things into a single step:

Sheets("mySheet").Range("A1").Resize(UBound(myArray) + 1, 1) = Application.WorksheetFunction.Transpose(myArray)

Hope that helped you all!

Cheers,

Excelman