Excel

Used Region Function

Ease of Use

Intermediate

Version tested with

2000 

Submitted by:

johnske

Description:

Function to work with a used region. 

Discussion:

CurrentRegion can be used to use areas with values, this is fine provided you want an area that doesn't have any empty columns or rows. Alternatively, UsedRange can be used when there are areas that contain empty columns or rows, but the problem is that UsedRange doesn't discriminate between values, formats, and such-like... so if you want the used area that contain values only you are left between a rock and a hard place. The UsedRegion function addresses that deficiency. i.e. It can be used for areas that contain values but may also have empty rows and/or columns. This can be handy where you only want to (say) search or print a given area, or, if want to put a border round it etc., and you want to ensure that you haven't missed/excluded any values sitting on their own somewhere way outside the main body of values. 

Code:

instructions for use

			

'***************************************************** 'The UsedRegion function below has the single optional argument ''StartCell', where StartCell is the top-left of the range you want to 'be considered. StartCell is used when you wish to be selective and 'only consider portions of the UsedRegion. In the absence of a given 'StartCell, the range A1 is taken to be the default StartCell i.e. the 'entire UsedRegion is then considered as being the default region. '***************************************************** Option Explicit '<< FUNCTION TO DETERMINE THE REGION CONTAINING VALUES >> Function UsedRegion(Optional StartCell As Range) As Range Dim LastCol&, LastRow&, Junction As Range '//empty sheet = error On Error GoTo NotFound '//find the last column LastCol = Cells.Find("*", SearchOrder:=xlByColumns, _ LookIn:=xlValues, SearchDirection:=xlPrevious).Column '//find the last row LastRow = Cells.Find("*", SearchOrder:=xlByRows, _ LookIn:=xlValues, SearchDirection:=xlPrevious).Row '//set the default start cell as A1 If StartCell Is Nothing Then Set StartCell = [A1] If StartCell.Row > LastRow Or StartCell.Column > LastCol Then '//end if the start cell is outside the used region MsgBox "Your start cell is outside the used region" End Else '//continue if the start cell is inside the used region Set Junction = Intersect(Columns(LastCol), Rows(LastRow)) Set UsedRegion = Range(StartCell.Address & ":" & Junction.Address) End If Exit Function NotFound: '//empty sheet MsgBox "There are no values on this sheet", , "No Used Region" '//message alert already given (just above) Application.DisplayAlerts = False End End Function '***************************************************** '<< A SIMPLE EXAMPLE USAGE >> Sub UsedRegionAddy() '//give the used region address MsgBox "The Used Region address is " & UsedRegion.Address End Sub '***************************************************** '<< ANOTHER SIMPLE EXAMPLE USAGE >> Sub EntireUsedRegion() '//colour the UsedRegion from A1 to the Junction UsedRegion.Interior.ColorIndex = 35 End Sub '***************************************************** '<< A LESS SIMPLE EXAMPLE USING THE OPTIONAL ARGUMENT >> Sub TheUsedRegion() '//more than one cell selected If Selection.Cells.Count > 1 Then MsgBox "Please select one cell only...", , "Ambiguous - Need One Cell" Exit Sub End If '//select the area starting from selection to the junction UsedRegion(ActiveCell).Select If Selection.Cells.Count = 1 Then MsgBox "(Only one cell in region)" End If End Sub '*****************************************************

How to use:

  1. Open an Excel workbook
  2. Select Tools/Macro/Visual Basic Editor
  3. In the VBE window, select Insert/Module
  4. Copy and paste the above code into this Module
  5. Now select File/Close and Return To Microsoft Excel
  6. Don't forget to save your changes...
 

Test the code:

  1. Select a cell, then Tools/Macro/Macros../UsedRegionAddy/Run
  2. Repeat with TheUsedRegion and EntireUsedRegion (there is a procedure in the attachment to undo the colouring)
  3. OR, download the example then select a cell, right-click and select the menu item 'Selective Used Region' for further testing
 

Sample File:

UsedRegion Function.zip 13.65KB 

Approved by mdmackillop


This entry has been viewed 258 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express