CODEWITHSUNDEEP
excelvba
HL8
HL8

Reputation: 1419

VBA Excel 2007 - Cells.Find

Can someone please explain how to read the below code to find last column and row with data?

Last_Column = Sheets("Combined").Cells.Find("", [a1], , , xlByColumns, xlPrevious).Column
Last_Row = Sheets("Combined").Cells.Find("", [a1], , , xlByRows, xlPrevious).Row

Thank you

Upvotes: 1

Views: 6684

Answers (1)

brettdj
brettdj

Reputation: 55682

To find the intersect of the last used column and row you should adapt your code to

  1. Search for "*" to match any wildcard not for "" which is for blank cells (NB: with this change your code above will work in a limited sense (see 2-3 below) if you run it from the combined sheet
  2. It is more robust not to assume the sheet has data, ie set ranges and then test they are Not Nothing

  3. When using a starting cell ie '[a1] you should specify the sheet name as well to esnure the code works if you are running it from different sheets, ie ws.[a1] below

    Sub FindLast()
Dim ws As Worksheet
Dim rng1 As Range
Dim rng2 As Range
Set ws = Sheets("combined")
Set rng1 = ws.Cells.Find("*", ws.[a1], xlValues, , xlByRows, xlPrevious)
Set rng2 = ws.Cells.Find("*", ws.[a1], xlValues, , xlByColumns, xlPrevious)
If Not rng1 Is Nothing Then
    MsgBox "Last cell is " & Cells(rng1.Row, rng2.Column).Address(0, 0)
Else
    MsgBox "No cells found"
End If
End Sub

Upvotes: 1

Related Questions