Best Method for LastRow/LastColumn In Excel

It’s been awhile since I’ve posted, but I’ve just started messing around with PasteBin and so I’ll post something I put there.

I think the most common beginner VBA question is “how do I find the last row of a sheet?” There are dozens of answers out there, but there are better ways to accomplish this. In my opinion, the best way is below using the below VBA script which I’ve turned into a formula you can paste into your file and get your answer. My post includes text stating why I believe it’s better. Other methods are vulnerable to uneven columns, usedrange being out of sync, or filtered rows. Using the FIND method avoids a lot of confusion.

This particular formula has some if statements so if a user selects an entire column or columns, it will return the lowest row in that range.

Excel should have this function!

 

My Excel Contest Video

I am in an Excel popularity contest (talk about an oxymoron). A couple Excel bloggers were invited to compete in “Excel Dashboard contest” and naturally this is what I spent last Saturday night doing. The winner is declared by how many YouTube likes each effort earns, which essentially means I have no chance of winning because a couple competitors have pretty large followings.

However, I would like to at least have a good showing, so if you have a moment, I would be most grateful if you gave my video a “like”. Requires a youtube or gmail account and just clicking the “like” (thumbs UP) button located above where the number of video plays is displayed.

Don’t feel like you need to watch the whole video unless you’re suffering from insomnia and are seeking a cure.

Two-Dimensional Sumif’s In Excel

Yesterday I needed to perform a two-dimensional sumif’s, and finding syntax on how to do this wasn’t easy (meaning it took more than 3 minutes). So I figured I’d flood the internet with one more example.

My Video below gives a short walk-through of it, but essentially you use SUMPRODUCT function. The syntax is basically SUMPRODUCT((Range1CONDITION=<VALUE>)*(Range2CONDITION=<VALUE>)*DATARANGE).

In my initial example, I use formula:

=SUMPRODUCT((B3:B12=I2)*(C2:F2=I3)*C3:F12)

Order doesn’t matter in the above statements. What the function is actually doing is looping through each cell in the DATARANGE, testing if all the criteria is true. If so, it assigns a 1 in the first two fields, and the test, for each cell in the range, and if true, it assigns a 1 to it.

Sumproduct is also available on Google Drive Sheets, OpenOffice. Not available on Excel 2003 and earlier.

Link to file used in video.