WinBatch Tech Support Home

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

WinBatch
OLE COM ADO CDO ADSI LDAP
OLE with Excel
plus
Samples from Users

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

Determine First and Last Row and Column Used

Using Find Method

At times Excel cannot properly calculate the used range. Some data may have been cleared, but Excel thinks that those cells are still in use. This UDF can get the actual current used range. This code includes all nonempty cells. 




#DefineFunction RealUsedRange(objSheet)
    ; Determines the actual used range on a worksheet.
    ; Returns -1 for each array elemebnt if worksheet is empty
    xlValues = -4163
    xlPart = 2
    xlByRows = 1
    xlByColumns = 2
    xlNext = 1
     xlPrevious = 2
    ArrUsedRange = ArrDimension(4)

    ; First Row
    objFind = objSheet.Cells.Find(::What="*", After=objSheet.Range("IV65536"), LookIn=xlValues, LookAt=xlPart, SearchOrder=xlByRows, SearchDirection=xlNext)
    If objFind == 0 Then ArrUsedRange[0] = -1 ;No First Row
    Else ArrUsedRange[0]  = objFind.Row

     ; First Column
    objFind  = objSheet.Cells.Find(::What="*", After=objSheet.Range("IV65536"), LookIn=xlValues, LookAt=xlPart, SearchOrder=xlByColumns, SearchDirection=xlNext)
    If objFind == 0 Then ArrUsedRange[1] = -1 ;No First Column
    Else ArrUsedRange[1]  = objFind.Column

    ; Last Row
     objFind  =  objSheet.Cells.Find(::What="*", After=objSheet.Range("A1"), LookIn=xlValues, LookAt= xlPart, SearchOrder=xlByRows, SearchDirection=xlPrevious)
    If objFind == 0 Then ArrUsedRange[2] = -1 ;No Last Row
    Else ArrUsedRange[2]  = objFind.Row

    ; Last Column
    objFind  = objSheet.Cells.Find(::What="*", After=objSheet.Range("A1"), LookIn=xlValues, LookAt=xlPart, SearchOrder=xlByColumns, SearchDirection=xlPrevious)
    If objFind == 0 Then ArrUsedRange[3] = -1 ;No Last Column
    Else ArrUsedRange[3]  = objFind.Column

    Return ArrUsedRange

#EndFunction


; At times Excel cannot properly calculate the used range. Some data may have been cleared, but Excel  thinks that those cells are still in use.
; This UDF can get the actual current used range. This code includes all nonempty cells.

objXLS = ObjectCreate("Excel.Application")
objXLS.Visible = @TRUE

excelfile = AskFilename( "Excel File", "C:\Temp\Data", "Excel Files|*.xls;*xlsx", "", 1 )
objXLS.Workbooks.Open (excelfile)

For x = 1 To objXLS.ActiveWorkbook.Worksheets.Count
   objSheet = objXLS.ActiveWorkbook.Worksheets(x)
   title = StrCat("Worksheet: ", objSheet.name)

   ArrUsedRange = RealUsedRange(objSheet)

   msg = StrCat("First used row: " , ArrUsedRange[0] )  ; first used row
   msg = StrCat(msg,@CRLF,"First used column: " , ArrUsedRange[1] ) ; first used column
   msg = StrCat(msg,@CRLF,"Last used row: ", ArrUsedRange[2] ) ; last used row
   msg = StrCat(msg,@CRLF,"Last used column: ", ArrUsedRange[3] ); last used column
   Message(title,msg)

   objCells = 0
   objSheet = 0
Next

objXLS.Quit
objXLS = 0
Exit

Using UsedRange

The UsedRange method is sometime unreliable 
objxl = ObjectCreate("Excel.Application")
objxl.visible = @TRUE
objwbks = objxl.workbooks

excelfile = "C:\Temp\Data\warrentya.xls"
objwbks.open (excelfile)

objawb = objxl.activeworkbook
objwshts = objawb.worksheets

For x = 1 To objwshts.count
   objsheet = objawb.worksheets(x)
   objsheet.UsedRange ; attempt to clean all non empty cells
   currwsname = objsheet.name
   title = StrCat("Worksheet: ", currwsname)
   msg = StrCat("First used row: " , objsheet.UsedRange.Row)  ; get the first used row
   msg = StrCat(msg,@CRLF,"First used column: " , objsheet.UsedRange.Column) ; get the first used column
   msg = StrCat(msg,@CRLF,"Last used row: ", objsheet.UsedRange.Rows(objsheet.UsedRange.Rows.Count).Row) ; transform last row of the range to absolute address
   msg = StrCat(msg,@CRLF,"Last used column: ", objsheet.UsedRange.Columns(objsheet.UsedRange.Columns.Count).Column ); transform last column of the range to absolute address
   Message(title,msg)
   objsheet = 0
Next

objXL.quit

objwshts = 0
objawb = 0
objwbks = 0
objxl = 0
Exit

Article ID:   W16605

File Created: 2012:04:10:09:03:08
Last Updated: 2012:04:10:09:03:08