Finding the Last Row or Column three different ways in Excel VBA - Code Included

Grab the Free VBA Quick Reference Guide
So how do you find the last row or column using VBA. In this video I show you 3 different ways to find that last row.

We use a
1) SepcialCells with the xlCellTypeLastCell for a Sheet
2) We do the same for a a current region range with the help of some math
3) We use the Cells.End(xlUP)

These all work well which is your favorite?

CODE:
===========
Sub LastRow()

slr = Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Row
scl = Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Column

rrw = Range("B12").CurrentRegion.Row + Range("B12").CurrentRegion.Rows.Count - 1
rcl = Range("B12").CurrentRegion.Column + Range("B12").CurrentRegion.Columns.Count - 1

crw = Cells(100, 2).End(xlUp).Row
'xlLeft doesnt work for .End

End Sub

[muralikrishnareddy]

Grate Information very use full keep rock..

[tykim]

Great Stuff! Thanks for making these Vids really helps alot

[idlevandal]

Hey, it's xlToLeft so code would be for your example - lastRow = Sheet1.Cells(12,

[RahulGupta]

thanks.
cells.specialcells method is way easlier to do it.

[beyhanismail]

Hello there. I can find the row below the last processed row on the worksheet. My question is how can I find the last row between the waistline of two columns. (A:D) find me the last row between the columns

[stamatarsenikos]

it doesn't work in my spreadsheet for some reason gives me row 10054 but the last row is 554. I deleted all rows under row 554 but it still bring me last row=10054. Any idea why?

[antranikkhokazian]

Hi dear
I have a worksheet where I have set of number in a column which I need to apply subtraction in the next column. the problem that sometimes I might have comments in the previous cell which if apply normal formula will consider the cell which has the comment as zero value and give a wrong subtraction result.
In your example, if I am standing on "b17" and wants to apply subtraction upper cell which has a value greater than zero avoiding the gap of empty cells; in your example will be (b17 - b15) while I have to apply that in the next column "c". any good way to do that?

[stamatarsenikos]

I figured it out. Specialcells will go to the last used cell which may be empty. for example if you changed the font in a cell at bottom it will go to this cell. Have to be very careful using this. Range.Find is a better method to avoid this.

[viveksview]

i need one help on vba on count with lookup please

[rishabhrajshetty]

Hi, How do you find the next empty column to fill data in?

[sunilchoudhary]

Send mein Vlookup drag the formula stil look until emplty

[seansantiagox]

This video is so blurry and the font so small that it is barely able to be read.