Excel Office 365 VBA Usedrange issue (32-bit version)

Andreas

Thank’s but I found a workaround that did the trick.

I needed to limit the cell range to constrain users to ONLY enter data into cells I wanted and then store all the values within that cell range into an multidimension array.

Since Usedrange doesn’t restrict to just cells with values in them I used the following to get what I needed:

vosheet.Range(m1GlobalConsts.gcLabelSheetRange).AutoFilter 1, "<>"

'   Assigns the filtered Autofilter Range to an array

vaArr01 = vosheet.Range(ActiveSheet.Range(m1GlobalConsts.gcLabelSheetRange).Rows. _        

SpecialCells(xlCellTypeVisible).Address).Value 

This gave me the desired result I was  looking for.

Well, the whole secret is what we should understand in the used range.

I guess you expect the used cells, but that is not the sense of this very special property.

Make a new file and execute your sub. You get A1 as result... but do you used anything... no.

Now, just hide row 10 and you get A10. now hide row 5 and you get A5:A10

Unhide all rows and hide column C and you get... nope, not C1, it is still A1! We can also hide column A and the used range is still A1.

Is there any logic behind that? Well yes it is. The official article is here, but not helpful:

https://docs.microsoft.com/en-us/office/vba/api...

If there is nothing in the sheet, the used range is A1. If you hide any row means the used range jumps to that row, because we've used that row.

If you have something in A1 the used range is enlarged till that row.

That is the surreal logic, not what one would expect.

For Excel it is more import to now where's the limit of rows, because at that point the calculation engine can stop the execution of formulas that refers to the whole column. It's by design, data should be entered in Excel row by row (the top row should contain headings).

Furthermore the used range is enlarged to cells that you really use, e.g. if you change, e.g. just change the font size of C5:E10 Excel has to store that information in your file.

Just for fun, after you've changed the font size, select the whole sheet, go into the Data tab and apply an Autofilter. Now you may have a feeling of how Excel works internally and what each function in a sheet must do.

If you have a sheet with random filled cells, it is really hard to determine the real last filled cell. You can use Range.Find with SearchOrder and Searchdirection

Sub Test()
  MsgBox RangeLastCell.Address(0, 0)
End Sub

Private Function RangeLastCell(Optional ByVal Where As Range) As Range
  'Returns the last used cell in Where
  Dim R As Range, C As Range
  If Where Is Nothing Then Set Where = ActiveSheet.UsedRange
  Set R = Where.Cells(Where.Rows.Count, Where.Columns.Count)
  If IsEmpty(R) And Not R.Address = Where.Cells(1, 1).Address Then
    Set C = Where.Find("*", After:=R, SearchOrder:=xlByColumns, _
      SearchDirection:=xlPrevious)
    If C Is Nothing Then
      Set RangeLastCell = Where(1, 1)
    Else
      Set R = Where.Find("*", After:=R, SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious)
      Set RangeLastCell = Where.Cells(R.Row - Where.Row + 1, C.Column - Where _
        .Column + 1)
    End If
  Else
    Set RangeLastCell = R
  End If
End Function

But that may fail if the sheet is protected.

Andreas.

Hi,

Look here: https://contexturesblog.com/archives/2012/03/01/select-actual-used-range-in-excel-sheet/

Additional Note:

Even formatting changes to the cells cause incorrect UsedRange address to  be returned

• changing gridline or cell background color