How do I use Excel VBA to get the last cell that contains data within a specific range, such as in columns A and B Range("A:B")
?
using Find
like below is useful as it
Nothing
identifies a blank rangeSpecialCells
range)change "YourSheet"
to the name of the sheet you are searching
Sub Method2()
Dim ws As Worksheet
Dim rng1 As Range
Set ws = Sheets("YourSheet")
Set rng1 = ws.Columns("A:B").Find("*", ws.[a1], xlValues, , xlByRows, xlPrevious)
If Not rng1 Is Nothing Then
MsgBox "last cell is " & rng1.Address(0, 0)
Else
MsgBox ws.Name & " columns A:B are empty", vbCritical
End If
End Sub
You can try several ways:
Dim WS As Worksheet
Dim LastCellA As Range, LastCellB As Range
Dim LastCellRowNumber As Long
Set WS = Worksheets("Sheet1")
With WS
Set LastCellA = .Cells(.Rows.Count, "A").End(xlUp)
Set LastCellB = .Cells(.Rows.Count, "B").End(xlUp)
LastCellRowNumber = Application.WorksheetFunction.Max(LastCellA.Row, LastCellB.Row)
End With
Dim WS As Worksheet
Dim LastCell As Range
Set LastCell = Range("A:B").SpecialCells(xlCellTypeLastCell)
The latter can sometimes be tricky and might not work as you wanted it to.
You can also have a look at Chip Pearson's page about this issue
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With