CephasOz

Determining worksheet data area

Overview Discussion
CephasOz

CephasOz

Active Member
Joined
Feb 18, 2020
Messages
283
Office Version
  1. 365
Platform
  1. Windows
CephasOz submitted a new Excel article:

Determining worksheet data area - Finding the last cell in a worksheet

There are a few standard ways to find the area containing data in a worksheet, but each has a problem.

1) Activesheet.Cells(1).CurrentRegion won't work correctly if there are blank rows or columns
2) Activesheet.Cells(Activesheet.Rows.Count, 1).End(xlUp).Row won't work correctly if there is data in a cell in a greater numbered row but in a different column.
3) Activesheet.UsedRegion isn't dynamic - it gets updated when Excel is good and ready
4) Using Find by either rows or columns only...
Click to expand...

Read more about this Excel article...
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
In answering a recent post (Will blank cells in the table mess up my macro?), I ended up with this code to return the smallest rectangular range that holds all filled cells. I believe it supports the intent of the OP here.

Code: 
Function ReturnFilledRectangularRegion(wks As Worksheet) As Range

    Dim lLastRow As Long, lLastCol As Long
    Dim lFirstRow As Long, lFirstCol As Long

   With wks
      lLastRow = .Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious, , , False).Row
      lLastCol = .Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious, , , False).Column
      lFirstRow = .Cells.Find("*", , xlFormulas, , xlByRows, xlNext, , , False).Row
      lFirstCol = .Cells.Find("*", , xlFormulas, , xlByColumns, xlNext, , , False).Column
      Set ReturnFilledRectangularRegion = .Range(.Cells(lFirstRow, lFirstCol), .Cells(lLastRow, lLastCol))
   End With
  
End Function
 
Last edited by a moderator:
The code is concise, and we agree in using Find, but the code for ReturnFilledRectangularRegion has a few problems. It falls over on an empty worksheet, and also doesn't return the starting cell A1 as part of the range if the data is all on the first row, even when A1 isn't empty.
 
Thanks for pointing out the deficiencies. There can never be enough testing. The code below corrects the problems you noted as well as the similar problem when A1 is not empty and all filled cells are in the same column.

Code: 
Function ReturnFilledRectangularRegion(wks As Worksheet) As Range
    'If there is a populated region, return the range, else return Nothing
    
    'Valid, although unexpected (by me) range formats will be returned if an entire
    '  row or column is involved:
    'Cell(s) Filled     Range Returned  Expected Range Format
    'E5,I18             $E$5:$I$18      $E$5:$I$18
    'XFD1               $XFD$1          $XFD$1
    'A1, XFD3           $1:$3           $A$1:$XFD$3
    'A1, XDFD1048576    $1:$1048576     $A$1:$XDFD$1048576
    'A1, B1048576       $A:$B           $A$1:$B$1048576

    Dim lLastRow As Long, lLastCol As Long
    Dim lFirstRow As Long, lFirstCol As Long
    
    With wks
        On Error Resume Next  'Since these will fail (error 91) if worksheet empty
        lLastRow = .Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious, , , False).row
        lLastCol = .Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious, , , False).Column
        lFirstRow = .Cells.Find("*", , xlFormulas, , xlByRows, xlNext, , , False).row
        lFirstCol = .Cells.Find("*", , xlFormulas, , xlByColumns, xlNext, , , False).Column
        If .Range("A1").Value <> vbNullString Then lFirstRow = 1: lFirstCol = 1
        If Err.number <> 0 Then
            Set ReturnFilledRectangularRegion = Nothing
        Else
            Set ReturnFilledRectangularRegion = .Range(.Cells(lFirstRow, lFirstCol), .Cells(lLastRow, lLastCol))
        End If
        On Error GoTo 0
        
    End With

End Function

Sub Test_ReturnFilledRectangularRegion()

    Dim rng As Range
    
    Set rng = ReturnFilledRectangularRegion(ActiveSheet)
    
    If rng Is Nothing Then
        MsgBox "No filled Cells in " & ActiveSheet.Name
    Else
        MsgBox ActiveSheet.Name & " filled range is " & rng.Address
    End If

End Sub
 
You need to ensure that there are no filters applied or the Find method will be give inconsistent results.
 
CephasOz said:
and also doesn't return the starting cell A1 as part of the range i
Click to expand...
If you use the After parameter in Find and set it as the last cell in the range then it will find A1, if you have it undefined then it defaults to the top/left cell in the range (i.e. A1 in this case) and begins the search After that cell.
 
MARK858 said:
If you use the After parameter in Find and set it as the last cell in the range
Click to expand...
Mmmm. But the point of the function is to find the last cell. If you need to use the last cell to find the first cell, so that you can find the last cell (which may be the first cell), you just go round and round.
 
CephasOz said:
But the point of the function is to find the last cell. I
Click to expand...
The point of the function is to find the last used cell.

The last cell of the Cells function is the bottom right cell on the sheet i.e. Cells(rows.count, columns.count) or in simple terms in later versions of Excel cell XFD1048576, the find needs to start after the last cell on the sheet (which loops to the first cell).

CephasOz said:
If you need to use the last cell to find the first cell, so that you can find the last cell (which may be the first cell), you just go round and round.
Click to expand...


Put data in A1 only of the Activessheet and run

VBA Code: 
Sub xxxx()
Debug.Print Cells.Find("*", Cells(Rows.Count, Columns.Count), xlFormulas, , xlByRows, xlPrevious, , , False).Address
End Sub
Does it return the cell or continue looping?
 
Also compare the results of the 2 codes below
VBA Code: 
Sub xxxx()
Debug.Print Cells.Find("*", Cells(Rows.Count, Columns.Count), xlFormulas, , xlByRows, xlNext, , False).Address
End Sub
VBA Code: 
Sub yyyyy()
Debug.Print Cells.Find("*", , xlFormulas, , xlByRows, xlNext, , , False).Address
End Sub
 
You must log in or register to reply here.

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top