Home
Search
 
What's New
Index
Books
Links
Q & A
Newsletter
Banners
 
Feedback
Tip Jar
 
 
 
 
 
 
 
 
Old Pages
 
Old Index
Site Map
What's New
 
Books
How To
Tips & Tricks
Tutorials
Stories
Performance
Essays
Links
Q & A
New in VB6
Free Stuff
Pictures
 
 
 
TitleFind the first and last used row and column in an Excel spreadsheet
KeywordsExcel, bounds, row, column
CategoriesOffice
 
A very common mistake is to dig through the rows and columns looking for the bounds of the used cells. A much better solution is to use the worksheet's UsedRange object. This object represents the range of cells in use. Use this object's Row, Column, Rows.Count, and Columns.Count properties.
 
Private Sub cmdLoad_Click()
Dim excel_app As Object
Dim excel_sheet As Object
Dim new_value As String
Dim first_row As Integer
Dim first_col As Integer
Dim num_rows As Integer
Dim num_cols As Integer

    ' Create the Excel application.
    Set excel_app = CreateObject("Excel.Application")

    ' Uncomment this line to make Excel visible.
'    excel_app.Visible = True

    ' Open the Excel spreadsheet.
    excel_app.Workbooks.Open FileName:=txtExcelFile.Text

    ' Check for later versions.
    If Val(excel_app.Application.Version) >= 8 Then
        Set excel_sheet = excel_app.ActiveSheet
    Else
        Set excel_sheet = excel_app
    End If

    ' Get and display the bounds.
    first_row = excel_sheet.UsedRange.Row
    first_col = excel_sheet.UsedRange.Column
    num_rows = excel_sheet.UsedRange.Rows.Count
    num_cols = excel_sheet.UsedRange.Columns.Count

    MsgBox "Rows: " & Format$(first_row) & _
        " - " & Format$(first_row + num_rows - 1) & vbCrLf _
            & _
        "Cols: " & Format$(first_col) & _
        " - " & Format$(first_col + num_cols - 1)

    ' Comment the rest of the lines to keep
    ' Excel running so you can see it.

    ' Close the workbook without saving.
    excel_app.ActiveWorkbook.Close False

    ' Close Excel.
    excel_app.Quit
    Set excel_sheet = Nothing
    Set excel_app = Nothing
End Sub
 
My book Microsoft Office Programming: A Guide for Experienced Developers explains tons of other tips and tricks for Excel programming and Office programming in general.
 
 
Copyright © 1997-2003 Rocky Mountain Computer Consulting, Inc.   All rights reserved.
  Updated