(Excel) speed-up a loop using dichotomic logic

I need a DEFECTS-FREE function that returns the last filled (with a value) row and column in an Excel sheet, supporting ALL the situations.

These methods fail:
- Usedrange fails because it includes unfilled cells with only changed borders
- Find... xlFormulas method fails because it doesn't include autofiltered rows
- xlUp method is not good because it acts only on a single specified column at a time
- xlDown method is not good because it doesn't pass through intermediate unfilled cells
- xlCellTypeLastCell has some of the above limitations (eg it doesn't include autofiltered rows)
- xlCellTypeVisible count doesn't support non-contiguous visible range of rows.

The only working method that I found is the COUNTA, that is an Excel function that returns a >0 number when there are filled cells in the given range.

So this is my current solution (all the credits to "fanpages"):

Option Explicit
Public Function objLast_Cell(Optional ByRef objWorksheet As Worksheet = Nothing) As Range

' --------------------------------------------------------------------------------------------------------------
' [ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28187684.html ]
'
' Question Channel: Experts Exchange > Software > Office / Productivity > Office Suites > MS Office > MS Excel
'
' ID:               28187684
' Question Title:   (Excel) reliable way to find the last row with a value,
'                   supporting hidden and autofiltered not contiguous rows
' Question Asker:   lucavilla                                 [ http://www.experts-exchange.com/M_3713400.html ]
' Question Dated:   2013-07-17 at 21:38:39
'
' Expert Comment:   fanpages                                   [ http://www.experts-exchange.com/M_258171.html ]
' Copyright:        (c) 2013 Clearlogic Concepts (UK) Limited                           [ http://NigelLee.info ]
' --------------------------------------------------------------------------------------------------------------

  Dim intColumn                                         As Integer
  Dim intLoop                                           As Integer
  Dim lngLoop                                           As Long
  Dim lngRow                                            As Long
  
  On Error GoTo Err_objLast_Cell
  
  Set objLast_Cell = Nothing
  
  If (objWorksheet Is Nothing) Then
     Set objWorksheet = ActiveSheet
  End If
  
  intColumn = objWorksheet.UsedRange.Column - 1 + objWorksheet.UsedRange.Columns.Count
  lngRow = objWorksheet.UsedRange.Row - 1& + objWorksheet.UsedRange.Rows.Count
  
  For lngLoop = lngRow To 1& Step -1&
      If Application.CountA(objWorksheet.Rows(lngLoop)) > 0& Then
         Exit For
      End If ' If Application.CountA(objWorksheet.Rows(lngLoop)) > 0& Then
  Next lngLoop
  
  lngRow = lngLoop
  
  For intLoop = intColumn To 1 Step -1
      If Application.CountA(objWorksheet.Columns(intLoop)) > 0 Then
         Exit For
      End If ' If Application.CountA(objWorksheet.Columns(intLoop)) > 0 Then
  Next intLoop
  
  intColumn = intLoop

Exit_objLast_Cell:

  On Error Resume Next
  
  If lngRow > 0& And _
     lngRow <= objWorksheet.Rows.Count And _
     intColumn > 0& And _
     intColumn <= objWorksheet.Columns.Count Then
     Set objLast_Cell = objWorksheet.Cells(lngRow, intColumn)
  Else
     Set objLast_Cell = Nothing
  End If ' If lngRow > 0& And lngRow <= Rows.Count And intColumn > 0& And intColumn <= Columns.Count Then
  
  Exit Function
  
Err_objLast_Cell:

  On Error Resume Next
  
  lngRow = 0&
  intColumn = 0
  
  Resume Exit_objLast_Cell
  
End Function

Open in new window



As you see there are two "Step -1" loops to find the last filled row and column within the UsedRange area.

How can I speed-up these two loops with dichotomic steps instead than stepping for single lines?
(consider in extreme that if row 1.000.000 have a border set, and last row with a value is 10, currently it steps through 999.990 rows! and while it could be fast inside VBA/Excel, it's very slow when run from an external programming environment like AutoIT because it requires launching a COM communication for each step/row)
lucavillaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

fanpagesCommented:
Hi,

I am not familiar with "AutoIT".

Why is there a need to launch a "COM communication for each step/row"?  Is that a limitation to the programming environment?  Can one global/public "COM communication" not be used used throughout the execution?

BFN,

fp.
fanpagesCommented:
A link to the previous question, for background information:

"(Excel) reliable way to find the last row with a value, supporting hidden and autofiltered not contiguous rows"
[ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28187684.html ]
lucavillaAuthor Commented:
Here is how it looks like in AutoIT:

Local $oX = _ExcelBookOpen("test.xlsm")
Local $sLastCell = FindLastCell($oX)
MsgBox(0, "", "Last cell address is: " & $sLastCell.address)

Func FindLastCell(ByRef $oExcel)

	Local $intColumn, $intLoop, $lngLoop, $lngRow, $objLast_Cell = ""

	$objWorksheet = $oExcel.ActiveSheet
	$intColumn = $objWorksheet.UsedRange.Column - 1 + $objWorksheet.UsedRange.Columns.Count
	$lngRow = $objWorksheet.UsedRange.Row - 1 + $objWorksheet.UsedRange.Rows.Count

	ConsoleWrite("1: " & $lngRow & @CRLF)
	For $lngLoop = $lngRow To 1 Step -1
		If $oExcel.Application.WorksheetFunction.CountA($objWorksheet.Rows($lngLoop)) > 0 Then ExitLoop
	Next

	$lngRow = $lngLoop

	ConsoleWrite("2: " & $intColumn & @CRLF)
	For $intLoop = $intColumn To 1 Step -1
		If $oExcel.Application.WorksheetFunction.CountA($objWorksheet.Rows($lngLoop)) > 0 Then ExitLoop
	Next

	$intColumn = $intLoop

	If $lngRow > 0 And _
		$lngRow <= $objWorksheet.Rows.Count And _
		$intColumn > 0 And _
		$intColumn <= $objWorksheet.Columns.Count Then
		$objLast_Cell = $objWorksheet.Cells($lngRow, $intColumn)
	EndIf

	Return $objLast_Cell
EndFunc


Func _ExcelBookOpen($sFilePath, $fVisible = 1, $fReadOnly = False, $sPassword = "", $sWritePassword = "")
	Local $oExcel = ObjCreate("Excel.Application")
	If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
	If Not FileExists($sFilePath) Then Return SetError(2, 0, 0)
	If $fVisible > 1 Then $fVisible = 1
	If $fVisible < 0 Then $fVisible = 0
	If $fReadOnly > 1 Then $fReadOnly = 1
	If $fReadOnly < 0 Then $fReadOnly = 0
	With $oExcel
		.Visible = $fVisible
		If $sPassword <> "" And $sWritePassword <> "" Then .WorkBooks.Open($sFilePath, Default, $fReadOnly, Default, $sPassword, $sWritePassword)
		If $sPassword = "" And $sWritePassword <> "" Then .WorkBooks.Open($sFilePath, Default, $fReadOnly, Default, Default, $sWritePassword)
		If $sPassword <> "" And $sWritePassword = "" Then .WorkBooks.Open($sFilePath, Default, $fReadOnly, Default, $sPassword, Default)
		If $sPassword = "" And $sWritePassword = "" Then .WorkBooks.Open($sFilePath, Default, $fReadOnly)

		; Select the first *visible* worksheet.
		For $i = 1 To .ActiveWorkbook.Sheets.Count
			If .ActiveWorkbook.Sheets($i).Visible = $xlSheetVisible Then
				.ActiveWorkbook.Sheets($i).Select()
				ExitLoop
			EndIf
		Next
	EndWith
	Return $oExcel
EndFunc

Open in new window


From a fast test it seems to me that it takes about 1 minute to loop for 1000 rows.
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

krishnakrkcCommented:
Hi

Does this help you ?

Function FINDLASTROW(Optional ByRef Wksht As Worksheet = Nothing) As Long
    
    Dim strAddress  As String
    Dim nmName      As Name
    
    If Wksht Is Nothing Then
        Set Wksht = ActiveSheet
        strAddress = Wksht.UsedRange.Address
    Else
        strAddress = "'" & Wksht.Name & "'!" & Wksht.UsedRange.Address
    End If
    
    On Error Resume Next
    Set nmName = Wksht.Parent.Names("_Last_Row_")
    If nmName Is Nothing Then
        Wksht.Parent.Names.Add "_Last_Row_", "=max(if(len(" & strAddress & "),row(" & strAddress & ")))", 0
    Else
        nmName.RefersTo = "=max(if(len(" & strAddress & "),row(" & strAddress & ")))"
    End If
    FINDLASTROW = Evaluate("_Last_Row_")
    
End Function

Open in new window


Kris
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
FYI: COM Automation indeed requires a lot of overhead for each object you access (cell, row, column, worksheet, ...), and you cannot apply operations to multiple cells as easy as you can inside of Excel sheets. Having to ask for as less data as possible is essential. It doesn't matter which tool you use - VBS, PowerShell, AutoIt, VB, ...
fanpagesCommented:
How can I speed-up these two loops with dichotomic steps instead than stepping for single lines?

As you are opening the file each time you are testing, suggesting saving it to reduce/recalculate the size of the UsedRange prior to checking the "Last Cell" will not be effective in this case.

Perhaps you could exercise some form of "binary chop" approach using the Application.WorksheetFunction.CountA(...) statement.

Check all the rows in the UsedRange, then half the quantity & test the latter half (50%-100% of the total rows in the UsedRange) with the CountA(...) function.

If a value greater than zero is returned, start at half of the tested range & increase by half again (check 75%-100%).   Repeat this until CountA(...) is zero, then work downwards in similar increments (50% of the tested range from the current lower threshold to the upper threshold) until CountA(...) is greater than zero again.

Repeat going up/down until you have a clear division between a single row with CountA(...) greater than zero, & an adjacent one with CountA(...) equal to zero.

This is only going to be effective with a sufficiently large set of rows to check where there are many potential blank rows towards the end of the UsedRange.  If this is not the case, the "binary chop" may well take longer to execute than the method you are currently using (simply stepping backwards from the last row until you reach the first non-blank row).

PS. I think I wrote a "Binary Chop" routine in an Experts-Exchange question a few years ago, if you care to search for it :)
lucavillaAuthor Commented:
fanpages I love you :)  you described what I meant with "dichotomic" logic.

Usedrange method alone will never work (as I wrote in my first post) because... if I set black borders on the cell XFD1000000... the UsedRange becomes "A1:XFD1000000" even if the last row with a value is 10!
and your original script would loop for 999.990 rows before finding that the last row is 10.

AutoIT seems to take 1 minute to cycle for 1000 rows, so 16 hours to cycle for 999.990 rows.

With dichotomic logic  (i.e. CountA.rows(500000:1000000), then CountA.rows(250000:499999), then CountA.rows(125000:249999), and so on...) it would do only.... around 20 cycles to find that the last row with a value is 10, correct?

The difficult part for me is to code the dichotomic logic, since it requires to reverse the direction even multiple times before arriving to the solution.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Some thoughts:

Using CountA is still not complete - if there is a formula, but no value results from it, it is counted in. Only CountIf with a complex condition can cope with all cases.

A cell is not interesting if its result value is
* empty
* 0
* ""
* "0"
* error
A column is not interesting if all cells are not. Same for a row. Those need to get removed if they are at the end of UsedRange.

For COM Automation it is usually much faster to use a Range reference, and shift it with the .Offset(-1) for "one row up" or .Offset(,-1) for "one column left" method successively. The Range object doesn't need (much) COM translations as it contains a direct pointer to the values. Using the Cells collection requires re-addressing on each access, causing more overhead.
lucavillaAuthor Commented:
ah wait, I have a better idea, in most real world cases it would be faster to start progressively this way:
CountA.rows(1000000:1000000), then CountA.rows(999998:999999), then CountA.rows(999994:999997), then CountA.rows(999986:999993), and so on....
because there's higher likelihood that the last used row is near to the last row of the UsedRange.

And I was wrong when I wrote that it requires to "reverse" the direction after the CountA find ">0" value.  It just have to restart with the same ceiling (high) row, lowering the range of rows.
Hmm I'm still not sure about what will be the fastest method....
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
The binary search method is something I had in mind, too, but it should be refined. I assume CountA result is sufficient in this case, to make it simple. Then the following schematics should be used to get fast results:

EmptyRows = UsedRange.Rows.Count - CountA(UsedRange.Rows)
Then CountA the range starting from row (UsedRange.Rows.Count - EmptyRows) to (UsedRange.Rows), and if 0 we are finished. Else there are some empty cells embedded, and we need to look further down. That can be done binary or starting from row (UsedRange.Rows.Count - EmptyRows) down (incrementing).

And the same would work for columns.
fanpagesCommented:
...because there's higher likelihood that the last used row is near to the last row of the UsedRange.

Does that not discount this statement:
...the UsedRange becomes "A1:XFD1000000" even if the last row with a value is 10!
and your original script would loop for 999.990 rows before finding that the last row is 10.

AutoIT seems to take 1 minute to cycle for 1000 rows, so 16 hours to cycle for 999.990 row

?

I love you (all) too *











* May (or may not) be true.
lucavillaAuthor Commented:
Qlemo, your post is gold for me!  and I'ld pay for a perfect solution (I'm on ODesk and Freelancer and I've Paypal).
However I'm hitting my head on this: why for such a simple and common problem (getting the last row or column with useful data) there's not an Excel native method nor good common solutions somewhere on the web?

I searched a lot and 99% of the proposed solutions are faulty, for the reasons I wrote in my first post, and who proposes them almost always is not even aware that his function is faulty (eg. when using autofilters).

I'm surprised about this...
cadman_dcartCommented:
Try this following code:

Dim intColumn                                         As Integer
  Dim intLoop                                           As Integer
  Dim lngLoop                                           As Long
  Dim lngRow                                            As Long
  Dim countloops                                        As Integer
  Dim flag                                              As Boolean
 
 
  Set objLast_Cell = Nothing
 
  If (objWorksheet Is Nothing) Then
     Set objWorksheet = ActiveSheet
  End If
 

 
  intColumn = objWorksheet.UsedRange.Column - 1 + objWorksheet.UsedRange.Columns.Count
  lngRow = objWorksheet.UsedRange.Row - 1& + objWorksheet.UsedRange.Rows.Count
 
  If intcolum >= lngRow Then
    countloops = intcolumnn
    intLoop = lngRow
   
  Else
    countloops = lngRow
    intLoop = intColumn
  End If
 
 flag = True
 

 Do While Not (Application.CountA(objWorksheet.Rows(countloops)) > 0)
      countloops = countloops - 1
      Do While flag
          If Application.CountA(objWorksheet.Columns(intLoop)) > 0 Then
             flag = False
             intColumn = intLoop
           End If
      Loop
 Loop
 lngRow = countloops
 intColumn = intLoop
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
fanpage & lucavilla,

if we translate the "likelihood" to be short below the row of (Startrow of UsedRange + CountA(UsedRange.Rows.Range("A:A"))), that should fit much better ;-).
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
lucavilla,

I guess the issue is that there are so many possible definitions of "empty" cells. To define a "empty" row or column, all cells need to be "empty", and so the definition is dynamic, because e.g. zero values shall be not treated the same as "". But since you can code almost any condition into CountIf, you can build your own "empty" function "easily" :D.
lucavillaAuthor Commented:
Yes fanpages, I believe that in real world "there's higher likelihood that the last used row is near to the last row of the UsedRange" but I made an example that is drastically on the opposite side for the only reason to explain that your current solution would take 16 hours with AutoIT because of the so many COM communications.

To say it all, I think that the ideal solution would also consider that in ral world when UsedRange has a final row of 1048576 or 65536 there is... an higher likelihood that the last used row is far from it  :)


Qlemo, does "CountA(UsedRange.Rows)" counts the cells with a value or the rows with a value? I always thought that it counts the cells, so I don't understand your logic using that...

About the Offset method, would it give a speed advantage versus the absolute Rows ranges?
fanpagesCommented:
Qlemo, your post is gold for me!  and I'ld pay for a perfect solution (I'm on ODesk and Freelancer and I've Paypal).

Careful, I may take you up on that offer! ;)  However, that would be against the Terms & Conditions of usage of the site (in effect when I became a member).

Seriously, I think for a majority of cases, the UsedRange is sufficient for many of the basic needs people would have.  Your surprise that the use of filters may invalidate some of the previously published routines you have seen could be simply because the routines were written for use within worksheets that did not use filtering.

Some routines that do take alternate, or extended, approaches are often tailored towards their environment; that is, if the worksheet being tested has more rows than columns, it may make sense to test column-by-column for empty cells, rather than row-by-row.  With worksheets that have data presented in more rows than columns, the reverse of this logic may be true.

I, for example, use a routine that has various parameters that can be set to restrict or extend the search criteria.  For example, one such parameter extends to the (named) worksheet's Shapes collection, so I can include Controls (like CommandButtons) in the "empty row/column" conditions.

Over time I have added to this routine, & made improvements so that it becomes a general "catch all" function.  However, that doesn't mean I haven't missed certain conditions as it has not been used on each & every worksheet I have encountered.

Looking for the perfect solution is only really valid until the next release of MS-Excel anyway, due to the addition of new functionality & in-cell attributes that may render a previous solution less-than-perfect.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
I'm certain the offset method improves performance significantly, as I have used it two weeks ago from PowerShell for exact that reason (with Cell being horribly slow, and Offset being faster, but still (yawn) slow).

UsedRanges.Rows of course will provide a collection of rows (including cells), and is not correct that way; I only used it as blurry pseudo-code.
lucavillaAuthor Commented:
cadman_dcard:
I get a "Errore di compilazione:  Utilizzo non valido di Property" error on line "Set objLast_Cell = Nothing"
cadman_dcartCommented:
Lucavilla,

just change this code:
   Set objLast_Cell = Nothing
   If (objWorksheet Is Nothing) Then ...............' delet it "
     Set objWorksheet = ActiveSheet
  End If.................delete it

to:  
  Set objLast_Cell = Nothing
  Set objWorksheet = ActiveSheet
lucavillaAuthor Commented:
cadman_dcart: done.
I obviously still get a "Errore di compilazione:  Utilizzo non valido di Property" error on line "Set objLast_Cell = Nothing"    :)
cadman_dcartCommented:
just wondering, i tried that code right now and it's work well.

as it is not necessary delete that line: Set objLast_Cell = Nothing
lucavillaAuthor Commented:
the routines were written for use within worksheets that did not use filtering.

Yes but what make my crazy is that I'm sure that most of who uses them doesn't even know it because these routines doesn't give an error when filtering is in use, so they will learn it the bad way, maybe when their macro permanently lost data.

I use my macros over many files of many users in my bank and these users can use autofilters, protection and so on.
I don't think to be alone in the world wide web to work in such a multiuser environment and I can't imagine how others in my situation could use LastRow and LastColumn functions that can return wrong numbers over files of third persons with important data.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
I'm convinced most aware of the issue will just switch off Autofilter prior to using the different methods, and then switch it on again ... The issue indeed is more of that most are unaware of the issue, and that is severe.
lucavillaAuthor Commented:
cadman_dcart: can you please test it on the sheet named "big" in my attached file?
It fails with an "Overflow" run-time error 6 on my Excel 2010.
test--2-.zip
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Replace all Integer with Long, as Integer is still 16bit, and so values are available from -32768 to +32767 only ...
cadman_dcartCommented:
Lucavilla, I did not find any attachment here.
lucavillaAuthor Commented:
Qlemo: unfortunately it's not that easy. Files are often protected and automatically disabling autofilter or launching a ShowAllData data FAILS in most protected files.
I could add a starting line that unprotect and a final line that reprotect them but, even ignoring the password possibility issue, how could I restore the protection with the same "allow" flags enabled? (there are many files that have non default enabled flags).

I also posted this question here: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28202924.html
but only got a partial workaround/solution.
lucavillaAuthor Commented:
Qlemo: replacing the two Integer with Long worked but I launched it inside Excel and after 1 minute it's still running.

cadman_dcart: refresh this page, I firstly forgot the attachement so I added it to the same post 1 minute later.
cadman_dcartCommented:
Lucavilla,

pls see little changes on the code: It's working well now.
Sub cadman_dcart()

Dim intColumn                                         As Integer
  Dim intLoop                                           As Integer
  Dim lngLoop                                           As Long
  Dim lngRow                                            As Long
  Dim countloops                                        As Double
  Dim flag                                              As Boolean

  Set objWorksheet = ActiveSheet
 
  intColumn = objWorksheet.UsedRange.Column - 1 + objWorksheet.UsedRange.Columns.Count
  lngRow = objWorksheet.UsedRange.Row - 1& + objWorksheet.UsedRange.Rows.Count
  
  If intcolum >= lngRow Then
    countloops = intcolumnn
    intLoop = lngRow
    
  Else
    countloops = lngRow
    intLoop = intColumn
  End If
  
 flag = True
 

 Do While Not (Application.CountA(objWorksheet.Rows(countloops)) > 0)
      countloops = countloops - 1
      Do While flag
          If Application.CountA(objWorksheet.Columns(intLoop)) > 0 Then
        
             flag = False
             intColumn = intLoop
           End If
      intLoop = intLoop - 1
      
      Loop
 Loop
 lngRow = countloops
 intColumn = intLoop

MsgBox (lngRow)
MsgBox (intColumn)

End Sub

Open in new window

Rob BrockettCommented:
Wow, on refreshing the page after drafting the below, I see that the comment count is much higher now!

hi Lucavilla,

It is good to have robust code. However, & you used this word yourself, you seem to be taking this to the extreme. I think I have read all the threads which are effectively the same topic & I have the same question as FP - why have you not kept this to a single detailed question?

It seems to me that the level of robustness is in the wrong place - it seems like an "ambulance at the bottom of the cliff" when perhaps there should be a "barrier built at the top of the cliff"!
Can more control/better design be applied to the spreadsheets that this code is to be used against?
(possible answer, on seeing the more recent comments, => develop & enforce spreadsheet design standards, which I'd expect within in a Bank after reading articles from sites like this).

You have asked for VBA code but in this question you mention AutoIT. Is the code slow if you call the vba macro from AutoIT instead of converting the vba code into an AutoIT script?
eg http://www.autoitscript.com/forum/topic/42221-running-excel-vba-macros-from-autoit/

Rather than relying on xlup when looking at sheets that may have autofilters applied, you can check the the size of the range that the autofilter or list object relates to:
eg
                Set RangeOfAutoFilter = ActiveSheet.AutoFilter.Range

'or
                    Set RangeOfAutoFilter = .ListObject.Range

Open in new window


You could then go use RangeOfAutoFilter to identify the bottom row of the autofilter or list (not just the last visible row). Knowing the bottom row may help in your shortcut/chop logic too.

The below code snippets give an idea of how I've used this code in the past as part of my QuickFilter sourced from here. NB: I've only included the snippets to show some context for the above lines of code and the below snippets aren't the whole of the finished product.
Option Explicit

Private Sub CheckForFilterRangeExistenceAndLocate()
Dim LHCol As Long
Dim RangeOfAutoFilter As Range
Dim CurrentCell As Range
Dim FieldToFilter As Long

Static RepeatTime As Boolean
    
    Set CurrentCell = ActiveCell

    With CurrentCell
        Select Case ActiveSheet.AutoFilterMode
            Case True
                Set RangeOfAutoFilter = ActiveSheet.AutoFilter.Range
                Select Case DoesIntersect(RangeOfAutoFilter, CurrentCell)
                    Case True
                        LHCol = RangeOfAutoFilter.Resize(1, 1).Column
                        Select Case LHCol
                            Case 1
                                FieldToFilter = .Column
                            Case Else
                                FieldToFilter = .Column - LHCol + 1
                        End Select
                    Case False
                        MsgBox "Active Column not within the autofilter range on the sheet therefore no changes made.", , _
                               "CELL NOT IN AUTOFILTER RANGE!"
                End Select
            Case False
                '11/03/2011, RB: added to allow use of QuickFilter on 2007 query tables (### untested in excel 2003 & earlier)
                If Not .ListObject Is Nothing Then
                    Set RangeOfAutoFilter = .ListObject.Range
                    FieldToFilter = .Column - RangeOfAutoFilter.Column + 1
                Else
                    'adds a filter range automatically as needed
                    FieldToFilter = .Column
                    Set RangeOfAutoFilter = Range(Cells(1, 1), LastCell())
                End If
        End Select
    End With
End Sub


Private Function DoesIntersect(rng As Range, curCell As Range) As Boolean
Dim isect As Range
    Set isect = Intersect(rng, curCell)
    'Used in caller macro to decide if code is to be actioned
    If Not isect Is Nothing Then DoesIntersect = True
    Set isect = Nothing
End Function

Private Function LastCell() As Range
'26/04/2010, NOTE: this version of the "LastCell" function doesn't include the "ws as worksheet" argument _
  because QuickFilter is only intended for the ACTIVESHEET & this is hardcoded into the function.
' Error-handling is here in case there is not any data in the worksheet
'sourced from http://www.beyondtechnology.com/geeks012.shtml
Dim LastRow As Long
Dim LastCol As Long
    
    On Error Resume Next
    With ActiveSheet
        ' Find the last real row
        LastRow = .Cells.Find(What:="*", _
                              SearchDirection:=xlPrevious, _
                              SearchOrder:=xlByRows).Row
        ' Find the last real column
        LastCol = .Cells.Find(What:="*", _
                              SearchDirection:=xlPrevious, _
                              SearchOrder:=xlByColumns).Column
        ' Finally, initialize a Range object variable for
        ' the last populated row.
        Set LastCell = .Cells(LastRow, LastCol)
        If LastCell Is Nothing Then Set LastCell = .Cells(1, 1)
    End With
    On Error GoTo 0
End Function

Open in new window


Rob
BitsqueezerCommented:
Hi,

the "UsedRange" property does not work because of the formattings in empty cells for this case, that's logic.

But Excel has a method to clean that up, unfortunately it needs a saving of the file. The following code is a method to make sure that the "UsedRange" property returns the correct value like the function of Fanpage:

    Dim objSheet As Excel.Worksheet
    Dim objWB As Workbook
    
    Sheets("big").Select
    Sheets("big").Copy
    Set objWB = ActiveWorkbook
    
    Set objSheet = objWB.Sheets(1)
    objSheet.Cells.ClearFormats
    On Error Resume Next
    Kill Environ("TEMP") & "\Tmp.xlsx"
    On Error GoTo 0
    objWB.SaveAs Environ("TEMP") & "\Tmp"
    objWB.Close
    Set objWB = Application.Workbooks.Open(Environ("TEMP") & "\tmp.xlsx", , True)
    Debug.Print objWB.Sheets("big").UsedRange.Address
    objWB.Close
    Kill Environ("TEMP") & "\tmp.xlsx"

Open in new window


The trick is: The sheet will be copied to a new workbook, then ALL formats of the complete spreadsheet will be deleted. Now the file will be saved (in the Windows temp folder) and reloaded and now "UsedRange" returns the correct value.

(Of course this is not a direct usable sub, you must adjust it with variables and use something else than Debug.Print - should only show the method, not more.)

In VBA of course a function like that of Fanpage would be the better way, it's faster than this. In case of external automation when the method of this function is too slow the approach with saving and reloading can be the fastest possible way as any other method would either need a VBA function to be called in a XLSM module or using the automation methods which you already saw that it is too slow from external.

BTW: The initial code of cadman_dcart shows why you:

a) should ALWAYS use "Option Explicit" in ALL modules in ANY VBA (Word, Excel,....) - you would have found different "not declared" variables because lot of typos
b) should use long integer instead of integer in all cases as we do not have memory problems today so you can be sure not to run into an overflow error

And in the end there was a endless loop in the middle...;-)

Moreover: In cases where you need the same calculation more than once in ANY procedure, save the result to a variable, that saves performance.

Cheers,

Christian
lucavillaAuthor Commented:
cadman_dcart: you code inside Excel takes about 30 seconds to cycle for 1 million lines, like the original code.  There's no advantage.   And in AutoIT I see no reasons to believe that it should have a speed advantage. Do you?

broro183: controlling the "design standards" is not a viable solution for two reasons.

1) in fact I already control the design of many files and I *wanted* to set different level of protection for example, allowing the use of autofilters for some (who require it) and disallowing it for others (who don't need it). And the solution to unprotect them and reprotect them in my macroes would require me to remember *how* to reprotect them, that would be to difficult even because my macroes often are not inside the same file. I've to many thing in my head to remember to change my macroes when I change the protection settings of some file and/or to maintain multiple different LastRow and LastColumn functions for different type of files. It would be a mess.

2) some files come every day via email attachment from external companies that would never collaborate or change something just for me.

Your AutoFilter.range solution doesn't work. It fails to detect the last filled row that is 100703.

Calling a macro from AutoIT: it's not a solution because some Excel files comes from external companies without my macro inside them and adding the macro inside them via keyboard simulation is qualitatively and visibly not acceptable...
test--3-.zip
lucavillaAuthor Commented:
Bitsqueezer: I manually tried to copy the million of lines of the UsedRange of my sheet and when I tried to "paste values" in a new sheet I immediately got an error of insufficient resources.
It was such a bad crash that even Skype crashed at the same time (!) with a never seen before "Disk I/O error."
(and my disk has 40GB free space)
BitsqueezerCommented:
Hi,

you should not copy the UsedRange and paste values, that would of course be a problem because you would use the clipboard of Windows - and that's a problem because this blows your data up a lot as Windows must be able to hold different formats of the table for different target applications.

If you look into the code, the "Copy" method of the worksheet is used, not the "Copy" method of a range object. That means: You create a new workbook with the same data in one step without using the clipboard (you MUST do that with the automation method, not any external copy and paste method!).

So the way I described worked without a problem with your demo file above inside VBA. You must only translate that to the methods of the external script language using the automation methods of Excel (I don't know AutoIT so I cannot help you with this).

Cheers,

Christian
lucavillaAuthor Commented:
Ah, thanks Christian for your clarification.
How much time did it take on your PC with Excel/vba?
BitsqueezerCommented:
A few seconds with your demo file (around four) with a Windows Vista on an Intel quad core, not very fast compared to nowadays available PCs.
cadman_dcartCommented:
Hi Lucavilla,

I have delete the inner do while loop to. I think it will make difference on the speed. Try change on the last part of my code staring from the do while loop. Remember to change the Integer to long to avoid overflow.
..........................................................................................................
Do While Not (Application.CountA(objWorksheet.Rows(countloops)) > 0)
      countloops = countloops - 1
     
   If flag Then
      intLoop = intLoop - 1
      If Application.CountA(objWorksheet.Columns(intLoop)) > 0 Then
          flag = False
          intColumn = intLoop
      End If
   End If
   
     
 Loop
 lngRow = countloops
 intColumn = intLoop

MsgBox (lngRow)
MsgBox (intColumn)
Rob BrockettCommented:
hi,

That's a shame that you can't enforce design standards but I certainly accept it's not possible in this case.

After a bit more thought, I decided to ignore the autofilter/list/table ranges because of the hidden/filtered cells issue.


Calling a macro from AutoIT: it's not a solution because some Excel files comes from external companies without my macro inside them and adding the macro inside them via keyboard simulation is qualitatively and visibly not acceptable...
Could you store the macro (written to act on the activesheet) in a different file (eg your personal xls(b)) which is always open when you run the AutoIT script?


I've had a go writing a "chop approach" macro using CountA. How does this compare in terms of speed & robustness?

Option Explicit

Sub AttemptAtARobustLastCellFinder_v2()
'inspired by FP's comments about a "binary chop" approach http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28203209.html#a39380467
'still subject to the limitations of CountA which Qlemo mentioned: http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28203209.html#a39380520

'
Const PercentStr As String = (".5,.3,.1,.05,.03,.01,.005,.003,.001,1")
Dim PercentArr As Variant    'this can probably be written better
Dim PercentageMultiplier As Double
Dim PercentInd As Long    'percent loop index
Dim Ws As Worksheet
Dim LastRow As Long
Dim LastCol As Long
Dim RowsInWs As Long
Dim ColsInWs As Long
Dim LoopInd As Long
Dim UpperLim As Long
Dim BlockSizer As Long

    Set Ws = ActiveSheet
    With Ws
        RowsInWs = .Rows.Count
        ColsInWs = .Columns.Count
    End With
    PercentArr = Split(PercentStr, ",")

    'run a loop to find the last row
    UpperLim = Application.WorksheetFunction.Min(RowsInWs, Ws.UsedRange.Rows.Count)
    For PercentInd = LBound(PercentArr) To UBound(PercentArr)

        PercentageMultiplier = CDbl(PercentArr(PercentInd))
        If PercentageMultiplier <> 1 Then
            BlockSizer = PercentageMultiplier * RowsInWs
        Else
            BlockSizer = 1
        End If

        For LoopInd = UpperLim To 1 Step -BlockSizer
            If (LoopInd - BlockSizer + 1) > 0 Then
                If Application.CountA(Ws.Range(LoopInd - BlockSizer + 1 & ":" & LoopInd)) Then
                    Exit For
                End If
            Else
                Exit For
            End If
        Next LoopInd

        UpperLim = LoopInd
    Next PercentInd
    LastRow = UpperLim

    'run a loop to find the last column
    UpperLim = Application.WorksheetFunction.Min(ColsInWs, Ws.UsedRange.Columns.Count)
    For PercentInd = LBound(PercentArr) To UBound(PercentArr)

        PercentageMultiplier = CDbl(PercentArr(PercentInd))
        If PercentageMultiplier <> 1 Then
            BlockSizer = PercentageMultiplier * ColsInWs
        Else
            BlockSizer = 1
        End If

        For LoopInd = UpperLim To 1 Step -BlockSizer
            If (LoopInd - BlockSizer + 1) > 0 Then
                With Ws
                    If Application.CountA(.Range(.Cells(LastRow, LoopInd - BlockSizer + 1), .Cells(LastRow, LoopInd))) Then
                        Exit For
                    End If
                End With
            Else
                Exit For
            End If
        Next LoopInd

        UpperLim = LoopInd
    Next PercentInd
    LastCol = UpperLim

    'User feedback
    Debug.Print "Last row = " & LastRow & vbNewLine & "Last column = " & LastCol & vbNewLine & "Address = '" & Ws.Name & "'!" & Ws.Cells(LastRow, LastCol).Address
    MsgBox "Last row = " & LastRow & vbNewLine & "Last column = " & LastCol & vbNewLine & "Address = '" & Ws.Name & "'!" & Ws.Cells(LastRow, LastCol).Address

    Set Ws = Nothing

End Sub

Open in new window


hth
Rob
lucavillaAuthor Commented:
Rob I get a run-time error 13 (Type mismatch) on this line:
PercentageMultiplier = CDbl(PercentArr(PercentInd))
Rob BrockettCommented:
hi Lucavilla,

It works for me. Maybe there is some sort of text conversion issue when you copy & paste.

Does it work for you in the attached file?

If not, can you please choose the Debug option when the error occurs, then press [alt + v + s] to display the Locals Window in the VBE & upload a snippet like the one that I have attached?
Hopefully there will be a difference between the values you see & the ones that I have highlighted.

Rob
test-v4.xlsm
Locals-Window-while-testing-Atte.PNG
lucavillaAuthor Commented:
Can you understand why even after a ActiveSheet.Cells.Clearformats the usedrange of the attached sheet still go down to line 1048576?
test-temp.zip
BitsqueezerCommented:
Hi,

"ClearFormat" is not enough. Excel resets the UsedRange property only if the document will be saved and reloaded. That's why I made the script above to save it as a temporary workbook and reload it.

Cheers,

Christian
lucavillaAuthor Commented:
I manually saved and reopened it after the ClearFormat and it's still on line 1048576   :(
Rob BrockettCommented:
Try ".Clear" instead of ".clearformat".

Rob
lucavillaAuthor Commented:
Tried  ActiveSheet.Cells.Clear,  saved,  reopened   and CTRL+END still does to cell DCP1048576
Rob BrockettCommented:
hi Lucavilla,

Did you try my code again?
Of course, it is only identifying the last cell, it contains no code to actually delete/clear the unused cells beyond the cell that it identifies.

Rob
BitsqueezerCommented:
Hi,

in the meantime I found a surprisingly easier way to reset the UsedRange:

Application.ActiveSheet.UsedRange

Open in new window


Use that as commandline and the range will reset without the need to save and load again.

Unfortunately that (and also the save method) doesn't work with sheets like your last where the last row is in the last possible row of Excel (not only in this case). Excel is not bug free and in my eyes it is a clear bug that your sheet cannot be reset with any method.

I also tried this:

Debug.Print Application.ActiveSheet.UsedRange.Rows.Count

Open in new window


Surprisingly it resets the UsedRange (after some seconds of waiting for the result), but not completely. The row is the same, but the column is now "A". You can go on and clear the complete sheet (including the data) and try to reset the UsedRange on any way - no chance. Excel will not reset the UsedRange here so this is a corrupt sheet which cannot be repaired (maybe there is a repair method but I don't know).

So in most cases, where the sheet is not that big as in your last example you could use the row above to reset the UsedRange so you don't need to save a temp file (the extra workbook must be created to be able to clear all formats, but after exploring the right UsedRange you can simply close it without saving).

Cheers,

Christian
lucavillaAuthor Commented:
Rob pls look at the attached screenshot.
It stills give the type mismatch run-time error 13.
07-08-2013-17-19-24.jpg
Rob BrockettCommented:
hi Lucavilla,

hmmm, that's strange?
Everything looks okay to me in your screenshot. Does the code compile for you if you press [alt + d + s] in the VBE (VBE - Debug menu - Compile VBA Project)?
If so, does it still compile after you add "Option Explicit" as the first line in every module & as the first line of every excel oggetti?
(I recommend always using "Option explicit". To make this happen for all new files & new modules you need to tick the "Require variable declaration" in VBE - Tools - Options... )

Is there any difference after (if you are willing?) you install & run Rob Bovey's Code Cleaner against your file?

Which version of Excel are you running & is it 32 or 64 bit?

Under VBE - Tools - References, are there any that have "missing" written next to them?

Christian,
I'm pleased you were having problems too. I couldn't even get it to reset to column A, but my lappy seems to be struggling a bit at the moment.
Does my code in this file work if you (or others) run it?
Or do you get an error like Lucavilla?

Thanks
Rob
Rob BrockettCommented:
hi,

I was looking through my list of (uninstalled) add-ins & noticed one called "xsformatcleaner.xla". I couldn't remember where I got it from so I did a Google search & found out that it was off an MS site (http://support.microsoft.com/kb/244435 ). I haven't downloaded it again but the version that I have includes the below snippet, which suggests row heights or column widths may be an issue. I hadn't read about or considered these as a possibility before now:

            Set ur = wksWks.Rows(r + 1 & ":" & wksWks.Rows.Count)
               ur.Clear
            'Reset row height which can also cause the lastcell to be innacurate
            ur.EntireRow.RowHeight = _
                  wksWks.StandardHeight
            Set ur = wksWks.Range(wksWks.Cells(1, c + 1), _
                  wksWks.Cells(1, 256)).EntireColumn
            'Reset column width which can also cause the lastcell to be innacurate
            ur.EntireColumn.ColumnWidth = _
                  wksWks.StandardWidth

Open in new window


Does it make any difference if you delete the "Big!_FilterDatabase" Name which exists for full columns in the file but without there being an autofilter on the page - looks like a mystery to me.
To see this I used JKP's Name Manager addin.

*****

Uhoh, I made an error in my logic in the v2 code. I will try & edit it, but in case I can't change the original post can you please update your versions as shown below?

'Change this line (searches a single row)
If Application.CountA(.Range(.Cells(LastRow, LoopInd - BlockSizer + 1), .Cells(LastRow, LoopInd))) Then
'To this line (searches the entire columns)
                    If Application.CountA(.Range(.Cells(1, LoopInd - BlockSizer + 1), .Cells(RowsInWs, LoopInd))) Then

Open in new window


hth
Rob
BitsqueezerCommented:
Hi Rob,

yes, I have the same error, but that's no wonder, because I'm from Germany where we use "," for decimal values and I think it's similar for Lucavilla.

The problem is that Microsoft has made the "C" functions (CDbl, CDate and so on) dependant from the Windows regional settings.

Set your Windows setting to "Germany" and you will have the same error, CDbl works then with ",5", but not with ".5".

But there is a really simple workaround: Use the Val function instead which is independent of the regional settings and always uses "." as decimal sign (this regional issue makes the convert functions really bad to use...you'll have a lot of fun in case of dates).

Another possibility is to simply create a value array instead of creating a string and split and convert it:

    PercentArr = Array(0.5, 0.3, 0.1, 0.05, 0.03, 0.01, 0.005, 0.003, 0.001, 1)

Open in new window


Cheers,

Christian
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
So so, noch'n Deutscher ...
BitsqueezerCommented:
Hallo QLemo, jawohl, schönen Gruß aus der Eifel...:-)
Rob BrockettCommented:
hi,

Thanks Christian :-)
Your explanation helped a lot.I have worked with dates & had international/regional issues* in the past. They can be frustrating.
(EDIT: *for dates, but I haven't experienced the comma/dot notation issue END EDIT)

I've modified the below code by removing the string array. I think the reason that I had used a string constant was to allow flexibility, to allow for change to be made easily & recognised & just once, at the top of the code.

Lukavilla,
Try this one, which I have changed into a function.

Function AttemptAtARobustLastCellFinder_v3(ws As Worksheet) As Range
'inspired by FP's comments about a "binary chop" approach http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28203209.html#a39380467
'still subject to the limitations of CountA which Qlemo mentioned: http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28203209.html#a39380520

Dim PercentArr As Variant    'this can probably be written better
Dim PercentageMultiplier As Double
Dim PercentInd As Long    'percent loop index
Dim LastRow As Long
Dim LastCol As Long
Dim RowsInWs As Long
Dim ColsInWs As Long
Dim LoopInd As Long
Dim UpperLim As Long
Dim BlockSizer As Long

    With ws
        RowsInWs = .Rows.Count
        ColsInWs = .Columns.Count
    End With
    'PercentArr = Split(PercentStr, ",")
    PercentArr = Array(0.5, 0.3, 0.1, 0.05, 0.03, 0.01, 0.005, 0.003, 0.001, 1)

    'run a loop to find the last row
    UpperLim = Application.WorksheetFunction.Min(RowsInWs, ws.UsedRange.Rows.Count)
    For PercentInd = LBound(PercentArr) To UBound(PercentArr)

        PercentageMultiplier = PercentArr(PercentInd)
        If PercentageMultiplier <> 1 Then
            BlockSizer = PercentageMultiplier * RowsInWs
        Else
            BlockSizer = 1
        End If

        For LoopInd = UpperLim To 1 Step -BlockSizer
            If (LoopInd - BlockSizer + 1) > 0 Then
                If Application.CountA(ws.Range(LoopInd - BlockSizer + 1 & ":" & LoopInd)) Then
                    Exit For
                End If
            Else
                Exit For
            End If
        Next LoopInd

        UpperLim = LoopInd
    Next PercentInd
    LastRow = UpperLim

    'run a loop to find the last column
    UpperLim = Application.WorksheetFunction.Min(ColsInWs, ws.UsedRange.Columns.Count)
    For PercentInd = LBound(PercentArr) To UBound(PercentArr)

        PercentageMultiplier = PercentArr(PercentInd)
        If PercentageMultiplier <> 1 Then
            BlockSizer = PercentageMultiplier * ColsInWs
        Else
            BlockSizer = 1
        End If

        For LoopInd = UpperLim To 1 Step -BlockSizer
            If (LoopInd - BlockSizer + 1) > 0 Then
                With ws
                    If Application.CountA(.Range(.Cells(LastRow, LoopInd - BlockSizer + 1), .Cells(LastRow, LoopInd))) Then
                        Exit For
                    End If
                End With
            Else
                Exit For
            End If
        Next LoopInd

        UpperLim = LoopInd
    Next PercentInd
    LastCol = UpperLim

    '    'User feedback
    Debug.Print "Last row = " & LastRow & vbNewLine & "Last column = " & LastCol & vbNewLine & "Address = '" & ws.Name & "'!" & ws.Cells(LastRow, LastCol).Address
    MsgBox "Last row = " & LastRow & vbNewLine & "Last column = " & LastCol & vbNewLine & "Address = '" & ws.Name & "'!" & ws.Cells(LastRow, LastCol).Address

    Set AttemptAtARobustLastCellFinder_v3 = ws.Cells(LastRow, LastCol)

End Function

Open in new window


I changed the above into a function so that I could start testing other code for deleting the identified rows. So far I have come up with the below which I haven't tested in detail (try it at your own risk!), but is proabably similar to what you already have...

Option Explicit

Sub ClearUnusedCellsAndResetTheRealLastCell()
Dim ws As Worksheet
Dim UsedRngLastCell As Range
Dim RealLastCell As Range
Dim RowDif As Long    'Row difference between the real last used cell & the number of rows in the ws
Dim ColDif As Long
Dim DummyVar As Long

    Set ws = ActiveSheet
    Set RealLastCell = AttemptAtARobustLastCellFinder_v3(ws)

    With ws.UsedRange
        Set UsedRngLastCell = ws.Cells(.Rows.Count, .Columns.Count)
        Debug.Print "UsedRngLastCell address:" & UsedRngLastCell.Address
    End With

    Stop
    'delete the unused rows & columns
    With RealLastCell
        Debug.Print "RealLastCell address:" & .Address
        RowDif = UsedRngLastCell.Row - .Row
        Debug.Print "Rowdif: " & RowDif
        If RowDif > 0 Then
            Debug.Print "Rows to delete: " & .Offset(1, 0).EntireRow.Resize(RowDif).Address
            With .Offset(1, 0).EntireRow.Resize(RowDif)
                .Clear
'                .Delete shift:=xlUp
            End With
        End If

        ColDif = UsedRngLastCell.Column - .Column
        Debug.Print "ColDif: " & ColDif
        If ColDif > 0 Then
            Debug.Print "Columns to delete: " & .Offset(0, 1).EntireColumn.Resize(, ColDif).Address
            With .Offset(0, 1).EntireColumn.Resize(, ColDif)
                .Clear
 '               .Delete shift:=xlLeft
            End With
        End If
    End With

    Stop
    'Reset the last cell
    DummyVar = ws.UsedRange.Rows.Count
    Debug.Print "DummyVar: " & DummyVar
    ws.Parent.Save

    With ws.UsedRange
        Set UsedRngLastCell = ws.Cells(.Rows.Count, .Columns.Count)
        Debug.Print "New UsedRngLastCell address:" & UsedRngLastCell.Address
    End With


    Set UsedRngLastCell = Nothing
    Set RealLastCell = Nothing
    Set ws = Nothing
    MsgBox "Done"

End Sub

Open in new window


hth
Rob
mlmccCommented:
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
lucavillaAuthor Commented:
ok at the end I opted for saving and removing protection and autofilter settings, use the find method, and restoring the protection and autofilter setting.
lucavillaAuthor Commented:
it was becoming too much complex so I decided for the easier solution posted in my last comment
Rob BrockettCommented:
Lucavilla,

Your original post included the question "How can I speed-up these two loops with dichotomic steps instead than stepping for single lines?". I think that "AttemptAtARobustLastCellFinder_v3" in my last post does show a method of speeding up the loops using the accepted approach with the CountA function. The regional settings issue has been removed based on Christian's feedback. Can you please confirm if this works?

I don't disagree that the suggestions from experts including myself are complex, however your original statement "I need a DEFECTS-FREE function that returns the last filled (with a value) row and column in an Excel sheet, supporting ALL the situations." requires, or at the very least, encourages complexity. The required complexity was discussed in various posts of the experts such as FP & Qlemo too, however, throughout your replies it seemed that you were sticking to that requirement until your last post.

Rob
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
http:#a39462616 is referring to something I mentioned in http:#a39380616 - switch off autofilter while trying to find the last cell ...
lucavillaAuthor Commented:
Qlemo, I can't switch off autofilter losing all my autofilter and protection settings so I had to use this solid solution:

1) this function to save all the protection settings and restore them with the addition of "UserInterfaceOnly:=True":
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28202924.html#a39381396

2) then this SaveFilters function to save all the autofilters settings:
http://stackoverflow.com/questions/9489126/in-excel-vba-how-do-i-save-restore-a-user-defined-filter

3) then the Find method to find the last filled cell

4) then the RestoreFilters function (see link above) to restore all the filter settings

The only remaining problem is when the sheet is password protected.

___

About my original request, I can't accept any the past answers but I'm still interested in it.

Bitsqueezer: my Excel is in italian.
Do you have a good solution that works without that line "PercentArr = Array(0.5, 0.3, 0.1, 0.05, 0.03, 0.01, 0.005, 0.003, 0.001, 1)"?
BitsqueezerCommented:
Hi,

I already posted a solution long time ago above so no, I do not have a better one. The mentioned row has no problem with local versions of Excel as this creates an array with decimal values, VBA always uses "." as decimal separator - it is no string in this case.

Cheers,

Christian
lucavillaAuthor Commented:
Rob and Bitsqueezer, can you please clarify what solution should I try?

I read again all the thread and only found a solution that clears cells/rows/columns formatting and then use the UsedRange but that's not acceptable for me as final (perfect...) solution... althought I appreciated the effort...
BitsqueezerCommented:
Hi,

in an imperfect application which has bugs like this there is no perfect solution, only solutions which fits in most cases - and sometimes there are only workarounds and sometimes you can do it only manually.

So it's up to you to choose a solution, you already got different hints which can solve the problem.

By the way: You should really answer just in time and not after that long time. We do not have a problem - you have it. After a month nobody is really interested in working through such a long thread to remember what happened and what was the problem and the discussion.

Cheers,

Christian
Rob BrockettCommented:
hi Lucavilla,

I need a DEFECTS-FREE function that returns the last filled (with a value) row and column in an Excel sheet, supporting ALL the situations.

After some brief testing I believe this version will:
- work when Regional Settings are set to Italian.
- return the correct value when the last cell contains =""
- return the correct cell when the row of the last cell is hidden.
- return the correct cell when the column of the last cell is hidden.
- return the correct cell when the row is hidden by an autofilter.
- return A1 when the sheet is empty.
- work faster than the code (from FP) in your original post, in situations where the last cell is not very near to the bottom of the spreadsheet. For example against your test file from an earlier post, the original code took 28 seconds whereas v4 (shown below) took 1 second.

Are there any situations that I have missed that would make the below v4 code return an incorrect result?


How can I speed-up these two loops with dichotomic steps instead than stepping for single lines?
I think the below code (v4) shows how the loops can be made faster, as per the last point in my above list. Here is a (v4) function that returns the Last Cell & I hope you can find a way to implement it (perhaps in conjunction with the code you are currently using).


Option Explicit

Sub WhatIsTheLastCell()
Dim FeedBackStr As String

    Debug.Print Now

    'change sheet as required
    '   With objLast_Cell
    With AttemptAtARobustLastCellFinder_v4(ActiveSheet)
        FeedBackStr = .Address
        Application.Goto .Cells(1, 1)
    End With

    Debug.Print FeedBackStr
    Debug.Print Now
    MsgBox FeedBackStr, vbOKOnly

End Sub

Function AttemptAtARobustLastCellFinder_v4(ws As Worksheet) As Range
'inspired by FP's comments about a "binary chop" approach http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28203209.html#a39380467
'still subject to the limitations of CountA which Qlemo mentioned: http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28203209.html#a39380520

Dim PercentArr As Variant    'this can probably be written better
Dim PercentageMultiplier As Double
Dim PercentInd As Long    'percent loop index
Dim LastRow As Long
Dim LastCol As Long
Dim RowsInWs As Long
Dim ColsInWs As Long
Dim LoopInd As Long
Dim UpperLim As Long
Dim BlockSizer As Long
Dim FirstRowOfUsedRng As Long

    With ws
        RowsInWs = .Rows.Count
        ColsInWs = .Columns.Count
    End With
    PercentArr = Array(0.5, 0.3, 0.1, 0.05, 0.03, 0.01, 0.005, 0.003, 0.001, 1)

    'run a loop to find the last row
    'v4, amended in case the first row of the used range is not Row 1.
    With ws.UsedRange
        UpperLim = Application.WorksheetFunction.Min(RowsInWs, .Cells(1, 1).Row - 1 + .Rows.Count)
    End With

    For PercentInd = LBound(PercentArr) To UBound(PercentArr)

        PercentageMultiplier = PercentArr(PercentInd)
        If PercentageMultiplier <> 1 Then
            BlockSizer = PercentageMultiplier * RowsInWs
        Else
            BlockSizer = 1
        End If

        For LoopInd = UpperLim To 1 Step -BlockSizer
            If (LoopInd - BlockSizer + 1) > 0 Then
                If Application.CountA(ws.Range(LoopInd - BlockSizer + 1 & ":" & LoopInd)) Then
                    Exit For
                End If
            Else
                Exit For
            End If
        Next LoopInd

        UpperLim = LoopInd
    Next PercentInd

    'v4: .max is used to allow for empty sheets
    LastRow = Application.WorksheetFunction.Max(1, UpperLim)

    'run a loop to find the last column
    'v4, amended in case the first column of the used range is not column 1.
    With ws.UsedRange
        UpperLim = Application.WorksheetFunction.Min(ColsInWs, .Cells(1, 1).Column - 1 + .Columns.Count)
    End With
    For PercentInd = LBound(PercentArr) To UBound(PercentArr)

        PercentageMultiplier = PercentArr(PercentInd)
        If PercentageMultiplier <> 1 Then
            BlockSizer = PercentageMultiplier * ColsInWs
        Else
            BlockSizer = 1
        End If

        For LoopInd = UpperLim To 1 Step -BlockSizer
            If (LoopInd - BlockSizer + 1) > 0 Then
                With ws
                    'Searches entire columns
                    'v4 corrected as per http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28203209.html#a39392583
                    If Application.CountA(.Range(.Cells(1, LoopInd - BlockSizer + 1), .Cells(RowsInWs, LoopInd))) Then
                        Exit For
                    End If
                End With
            Else
                Exit For
            End If
        Next LoopInd

        UpperLim = LoopInd
    Next PercentInd

    'v4: .max is used to allow for empty sheets
    LastCol = Application.WorksheetFunction.Max(1, UpperLim)

    '    'User feedback for testing
    Debug.Print "Last row = " & LastRow & vbNewLine & "Last column = " & LastCol & vbNewLine & "Address = '" & ws.Name & "'!" & ws.Cells(LastRow, LastCol).Address
    '    MsgBox "Last row = " & LastRow & vbNewLine & "Last column = " & LastCol & vbNewLine & "Address = '" & ws.Name & "'!" & ws.Cells(LastRow, LastCol).Address

    Set AttemptAtARobustLastCellFinder_v4 = ws.Cells(LastRow, LastCol)

End Function

Open in new window


hth
Rob

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lucavillaAuthor Commented:
broro183/Rob THANKS! I made a few test with it and I'm suprised for how well and fast it works!

I'm convinced... I leave my alternative solution and will use this!

(and I can live with the "limitations of CountA which Qlemo mentioned: http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28203209.html#a39380520")
lucavillaAuthor Commented:
Excellent+++   :)
Rob BrockettCommented:
hi Lucavilla,

I'm pleased it works for you. Thank you for the points :-)
Just out of curiosity, does my code still perform reasonably "when run from an external programming environment like AutoIT"?

After scanning back over the thread I have to say this was quite a group effort, even if we came to some conclusions independently. My v4 code could probably be written more efficiently but I was learning as I went so I'm done for now & will stop at v4.

Some of the things I learnt were:
- the concept of binary chop (which I partially respected)!
- that a worksheet's usedrange doesn't have to start at A1.
- that Regional Settings affect more than just dates for example it influences how some specific VBA functions (CDbl etc) work.
Thanks everyone :-)

Rob
lucavillaAuthor Commented:
broro183: about calling it from external programming environment like AutoIT, I had to convert it in COM instructions but I decided for a simplified alternative approach:
I (manually) inserted your function in a global_macroes.xlsa (Excel add-in) file, than in AutoIT I just put a single-line COM instruction that adds a reference to that file inside the working Excel file and then with another single-line COM instruction I run your macro :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.