VBA Express : Excel - Using Functions to Get a Range

Excel

Using Functions to Get a Range

Ease of Use

Intermediate

Version tested with

2000

Submitted by:

Brandtrock

Description:

Define the first and last used row using a function. Define the first and last used column using a function. Utilize the results of these functions to grab the range of data (includes hard coded data as well as formulas) on the worksheet.

Discussion:

Going to the bottom of the sheet (or far right) and then back will indicate which row or column your data/formulas end in. When your data/formulas don't always start or end in the same place; for instance, when an assignment has been given to a class or a group of employees and the layouts may vary significantly; a way to grab a nice rectanglular shaped range is needed. By identifying the first and last row and the first and last column that contain data, this can be done easily enough.

Code:

instructions for use

			Option Explicit 
Dim MyRange As Range 
 
Sub MoveIt() 
    Set MyRange = Range((Cells(xlFirstRow, xlFirstCol)), (Cells(xlLastRow, xlLastCol))) 
    MyRange.Copy Destination:=Sheets("Sheet2").Range("B4") 
     'this range can be changed to whatever you like
     
End Sub 
Function xlFirstCol(Optional WorksheetName As String) As Long 
     
     '  find the first populated column in a worksheet
     
    If WorksheetName = vbNullString Then 
        WorksheetName = ActiveSheet.Name 
    End If 
    With Worksheets(WorksheetName) 
        xlFirstCol = .Cells.Find("*", .Cells(1), xlFormulas, _ 
        xlWhole, xlByColumns, xlNext).Column 
    End With 
     
End Function 
Function xlFirstRow(Optional WorksheetName As String) As Long 
     
     '  find the first populated row in a worksheet
     
    If WorksheetName = vbNullString Then 
        WorksheetName = ActiveSheet.Name 
    End If 
    With Worksheets(WorksheetName) 
        xlFirstRow = .Cells.Find("*", .Cells(1), xlFormulas, _ 
        xlWhole, xlByRows, xlNext).Row 
    End With 
     
End Function 
 
Function xlLastRow(Optional WorksheetName As String) As Long 
     
     '    find the last populated row in a worksheet
     
    If WorksheetName = vbNullString Then 
        WorksheetName = ActiveSheet.Name 
    End If 
    With Worksheets(WorksheetName) 
        xlLastRow = .Cells.Find("*", .Cells(1), xlFormulas, _ 
        xlWhole, xlByRows, xlPrevious).Row 
    End With 
     
End Function 
 
Function xlLastCol(Optional WorksheetName As String) As Long 
     
     '    find the last populated column in a worksheet
     
    If WorksheetName = vbNullString Then 
        WorksheetName = ActiveSheet.Name 
    End If 
    With Worksheets(WorksheetName) 
        xlLastCol = .Cells.Find("*", .Cells(1), xlFormulas, _ 
        xlWhole, xlByColumns, xlPrevious).Column 
    End With 
End Function

How to use:

Open an Excel Workbook (make sure there are at least two sheets in it).
Copy the code above.
Press Alt + F11 to open the Visual Basic Editor (VBE).
From the Menu, choose Insert-Module.
Paste the code into the right-hand code window.
Close the VBE, save the file if desired.
Press Alt + F8 and select 'MoveIt' from the listing of macros.

Test the code:

The attached example file has data beginning in Column B and going through Column N with the exception of Column H. There is data in Rows 2-5 and 7-10. The functions will flag Row 2 as first row, Row 10 as last row, Column B as first column, and Column N as last column. The range that will be copied is B2:N10. Try adding data or formulas to the sheet and see how the range is adjusted.

Sample File:

4 Corners.zip 11.41KB

Approved by mdmackillop

This entry has been viewed 284 times.