|
|
Title | Find the first and last used row and column in an Excel spreadsheet |
Keywords | Excel, bounds, row, column |
Categories | Office |
|
|
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.
|
|
|
|
|
|