Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How can I identify UsedRange OR last/first column/row etc.

Excel How To

How can I identify UsedRange OR last/first column/row etc.

by  Loomah  Posted    (Edited  )
A common problem is that UsedRange isn't 100% reliable

The following code will find the first and last used cells and then select a contiguous range between them.

Code: 
[color blue]Sub[/color] FindUsedRange()
    [color blue]Dim[/color] LastRow [color blue]As Long
    Dim[/color] FirstRow [color blue]As Long
    Dim[/color] LastCol [color blue]As Integer
    Dim[/color] FirstCol [color blue]As Integer[/color]

    [color green]' Find the FIRST real row[/color]
    FirstRow = ActiveSheet.Cells.Find(What:="*", _
      SearchDirection:=xlNext, _
      SearchOrder:=xlByRows).Row
      
    [color green]' Find the FIRST real column[/color]
    FirstCol = ActiveSheet.Cells.Find(What:="*", _
      SearchDirection:=xlNext, _
      SearchOrder:=xlByColumns).Column
    
    [color green]' Find the LAST real row[/color]
    LastRow = ActiveSheet.Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByRows).Row

    [color green]' Find the LAST real column[/color]
    LastCol = ActiveSheet.Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByColumns).Column
        
[color green]'Select the ACTUAL Used Range as identified by the
'variables identified above[/color]
ActiveSheet.Range(Cells(FirstRow, FirstCol), _
    Cells(LastRow, LastCol)).Select
[color blue]End Sub[/color]

It is possible to use the elements of this Sub in part and to adapt them. For instance to find the first empty row to insert data use the LastRow element and add 1.
eg
Code: 
[color blue]Sub[/color] MIT()
[color blue]Dim[/color] lRow [color blue]As Long[/color]
    [color green]' Find the FIRST EMPTY row by adding 1 to the last row[/color]
    lRow = ActiveSheet.Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByRows).Row [b]+ 1[/b]
    
    [color green]'Paste the data into the first
    'COMPLETELY empty row[/color]
    ActiveSheet.Paste Destination:=Cells(lRow, 1)
[color blue]End Sub[/color]

This is an adaptation of code by Rodney Powell (xl mvp) who in turn adapted it from Bob Umlas(again xl mvp). The method utilised here (... .Find(What:="*", ...) I have found useful in many more instances, such as identifying non blank rows/columns etc.

Amended 8 Mar 07 following GlennUK's comments about my deliberate mistake in not initialising FirstCol whilst initialising FirstRow twice. Well spotted Glenn, it must have only been like that for about 4 years!!
Then again on 24 March 07 as I'm quite obviously a complete muppet!
;-)
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top