1

I want to quickly be able to find the right most column and last row of a non contiguous range of data without using UsedRange. I have not found a quicker way than this method out there. Any ideas?

Function Range_Find_Method(ws As Worksheet)
Dim lRow As Long
Dim lCol As Long

With ws
    lRow = .Cells.Find(What:="*", _
                    After:=.Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row

    lCol = .Cells.Find(What:="*", _
                    After:=.Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByColumns, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Column
End With

Debug.Print lRow
Debug.Print lCol

end function

Thank you

Community
  • 1
  • 1
dadler
  • 145
  • 12
  • 3
    Have you looked at the following[Ron De Bruin LastRow, LastColumn](https://www.rondebruin.nl/win/s9/win005.htm) – QHarr Dec 12 '17 at 11:00
  • @QHarr - my idea as well :) – Vityata Dec 12 '17 at 11:00
  • @Vityata Yes. Always looking at his stuff. I've never considered it in terms of which is quicker but rather which suits the layout of my data (which in turn might impact which is quickest (shortest path kind of thing)). – QHarr Dec 12 '17 at 11:02
  • @QHarr - I knew the code was either from there or from CPearson. – Vityata Dec 12 '17 at 11:03
  • @QHarr , thank you for the prompt response! Adding it to my repertoire. – dadler Dec 12 '17 at 11:11
  • @Luuklag - Interesting! I knew that UsedRange shouldn't be used, but never knew *why* . Thanks – dadler Dec 12 '17 at 11:12

0 Answers0