Trying to get actual UsedRange object

[mjolnirmarkiv]

Hello there,

I have a tiny problem and won't refuse the help of people more experienced than me. I'm trying to get a used range in Excel and don't like behaviour of $excel_book.ActiveSheet.UsedRange which returns used range including some formated cells with no data in them while I need a range object that encompasses all cells between first (top-left) and last (bottom-right) non-empty cells on the worksheet (of course there could be empty cells inside, but it's important not to encompass area larger than that just because some empty cell was painted at the end of the worksheet).

 

Create Test.xlsx and run the script from the same dir for testing purposes, I need to assign $used_range to smth that would suit the above mentioned requirement:

#include <Array.au3>
#include <Excel.au3>

Global $excel, $excel_file = @ScriptDir & "\Test.xlsx"
Global $excel_book = _Excel_BookAttach ($excel_file)
If @error Then
  $excel = _Excel_Open (false, false, false, true, true)
  $excel_book = _Excel_BookOpen ($excel, $excel_file)
EndIf

Global $used_range = $excel_book.ActiveSheet.UsedRange
GLobal $used_range_data = _Excel_RangeRead ($excel_book, Default, $used_range)
If @error Then MsgBox (48, "Error code:", @error)

If IsObj ($excel) Then _Excel_Close ($excel)

_ArrayDisplay ($used_range_data)

Edited October 9, 2014 by mjolnirmarkiv

[mjolnirmarkiv]

Answering my own question, I've come up with this function.

It looks kinda ugly and not very fast but it's working. If you come up with smth better, do let me know.

Func UsedRangeStripBlanks ($p_used_range)
  If not IsObj ($p_used_range) Then Return SetError (1, 0, $p_used_range)

  Local $new_size[4]

  $i = 1
  While $p_used_range.Rows($i).SpecialCells($xlCellTypeBlanks).Count = $p_used_range.Rows($i).Cells.Count
    $i += 1
  WEnd
  $new_size[0] = $i - 1

  $i = $p_used_range.Rows.Count
  While $p_used_range.Rows($i).SpecialCells($xlCellTypeBlanks).Count = $p_used_range.Rows($i).Cells.Count
    $i -= 1
  WEnd
  $new_size[2] = $i - $new_size[0]

  $i = 1
  While $p_used_range.Columns($i).SpecialCells($xlCellTypeBlanks).Count = $p_used_range.Columns($i).Cells.Count
    $i += 1
  WEnd
  $new_size[1] = $i - 1

  $i = $p_used_range.Columns.Count
  While $p_used_range.Columns($i).SpecialCells($xlCellTypeBlanks).Count = $p_used_range.Columns($i).Cells.Count
    $i -= 1
  WEnd
  $new_size[3] = $i - $new_size[1]

  Return $p_used_range.Offset($new_size[0], $new_size[1]).Resize($new_size[2], $new_size[3])
EndFunc

Edited October 10, 2014 by mjolnirmarkiv

[water]

When you search the web for "excel last used cell visual basic" you will find a lot of helpful methods. Maybe one of them solves your problem a bit faster.

I will add a new function to the next version of the Excel UDF to get some special ranges.

[mjolnirmarkiv]

Yes, I've read it and come up with this. It seems like a tad faster than previous one, but not by much, nevertheless it fits me:

expandcollapse popup

Func RangeStripBlanks ($p_range)
  If not IsObj ($p_range) Then Return SetError (1, 0, $p_range)

  Local $start_cell, $end_cell, $range_rect[4]
  Local $row_count = $p_range.Rows.Count, $col_count = $p_range.Columns.Count
  Local $xlDown = -4121, $xlToLeft = -4159, $xlToRight = -4161, $xlUp = -4162

  ;Left (column) coordinate of top-left cell
  $i = 1
  Do
    $start_cell = $p_range.Cells(1, $i)
    $end_cell   = $start_cell.End($xlDown)
    $i += 1
  Until $start_cell.Text <> "" or $end_cell.Text <> "" or $i > $col_count
  $range_rect[0] = $start_cell.Column

  ;Top (row) coordinate of top-left cell
  $i = 1
  Do
    $start_cell = $p_range.Cells($i, 1)
    $end_cell   = $start_cell.End($xlToRight)
    $i += 1
  Until $start_cell.Text <> "" or $end_cell.Text <> "" or $i > $row_count
  $range_rect[1] = $start_cell.Row

  ;Left (column) coordinate of bottom-right cell
  $i = 0
  Do
    $start_cell = $p_range.Cells($row_count, $col_count - $i)
    $end_cell   = $start_cell.End($xlUp)
;    MsgBox (0, "", $start_cell.Row & ", " & $start_cell.Column & ": " & $start_cell.Text & "; " & $end_cell.Row & ", " & $end_cell.Column & ": " & $end_cell.Text)
    $i += 1
  Until $start_cell.Text <> "" or $end_cell.Text <> "" or $i > $col_count - 1
  $range_rect[2] = $start_cell.Column

  ;Top (row) coordinate of bottom-right cell
  $i = 0
  Do
    $start_cell = $p_range.Cells($row_count - $i, $col_count)
    $end_cell   = $start_cell.End($xlToLeft)
    $i += 1
  Until $start_cell.Text <> "" or $end_cell.Text <> "" or $i > $row_count - 1
  $range_rect[3] = $start_cell.Row

  Return $p_range.Offset($range_rect[1] - $p_range.Rows(1).Row, $range_rect[0] - $p_range.Columns(1).Column).Resize($range_rect[3] - $range_rect[1] + 1, $range_rect[2] - $range_rect[0] + 1)
EndFunc

Edited October 17, 2014 by mjolnirmarkiv