Home
Search
 
What's New
Index
Books
Links
Q & A
Newsletter
Banners
 
Feedback
Tip Jar
 
C# Helper...
 
XML RSS Feed
Follow VBHelper on Twitter
 
 
MSDN Visual Basic Community
 
 
 
 
 
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-2010 Rocky Mountain Computer Consulting, Inc.   All rights reserved.
  Updated