Sign in to follow this  
Followers 0
mjolnirmarkiv

Trying to get actual UsedRange object

4 posts in this topic

#1 ·  Posted (edited)

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 by mjolnirmarkiv

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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 by mjolnirmarkiv

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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:

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 by mjolnirmarkiv

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0