Code Contest: Submissions And Voting Information

7 Comments:

1. Jon Peltier writes:
   December 26, 2008 at 10:07 pm

   No offense, but item 2 is rather verbose. My ten-liner returns the last row used below any range, one or more columns wide:

   Function LastRow(rTest As Range) As Long
     Dim lTest As Long
     Dim iCol As Range
     For Each iCol In rTest.Columns
       With rTest.Parent.Cells(65536, iCol.Column)
         lTest = IIf(.End(xlUp).Row > lTest, .End(xlUp).Row, lTest)
       End With
     Next
     LastRow = lTest
   End Function

   Ten more lines tells you the last column:

   Function LastCol(rTest As Range) As Long
     Dim lTest As Long
     Dim iRow As Range
     For Each iRow In rTest.Rows
       With rTest.Parent.Cells(iRow.Row, "IV")
         lTest = IIf(.End(xlToLeft).Column > lTest, .End(xlToLeft).Column, lTest)
       End With
     Next
     LastCol = lTest
   End Function

   These need adjustments for Excel 2007's larger grid. In the first routine, replace 65536 with rTest.Parent.Rows.Count, and in the second, replace "IV" with rTest.Parent.Columns.Count.
2. Peder Schmedling writes:
   January 5, 2009 at 4:33 am

   @JP: yes, I agree that the function can appear somewhat verbose and tedious. Most of the comments where added for illustration purposes due to the fact that the procedure was to be posted on the web, hopefully this helps readers new to VBA to understand the inner workings of it

   Down to business, your "ten-liner" is surely compact and elegant but consider if the row containing the last used cell for a given column is hidden.. Your code would then yield the "wrong" result as my routine would detect this and return the correct row number. I say "wrong" here because this is a per-construction judgment, if one is comfortable with disregarding hidden cells/rows it's absolutely fine to use the "ten-liner". On a side-note I can mention that the "ten-liner" seems to handle hidden columns just fine, the problems occur only when the row of the last used cell is hidden.

   Another point is that my function returns zero if there's no last used cell in the column (i.e. no content in any cells), whereas the "ten-liner" returns one (this is also a per-construction thing though).

   I haven't tested/debugged my function using Excel 2007 (as we are still using 2003 where I work). A quick test I performed indicates that the function seems to work fine in Excel 2007 as well..
3. Jon Peltier writes:
   January 5, 2009 at 1:38 pm

   Peder -

   Comments take up less than half of your code.

   Changing 1 to 0 if a row has no values at all is a very short if statement.

   However, your observation that .End misses hidden cells is a fact that I didn't know. Learn something every day.

   I looked at your code, and this part concerned me:

   Do While IsEmpty(rng)
     Set rng = rng.Offset(-1)
   Loop

   Any time a range is looped cell-by-cell, there is a chance that performance will be impacted. I tested with a used range that was defined by the hidden cell in my working column, and the result was instantaneous. However, when I placed a dummy value in the next column, to make the used range 65500 rows tall, there was a noticeable delay of around 0.5 seconds (I used a timer to measure the duration, and it ranged from 0.46875 to 0.59375 seconds in half a dozen trials).

   I instead devised a technique that loads the range to be tested into a VB array, and searches the array for data. This is faster because it reads from the sheet one time rather than one time per loop. In a number of trials this routine tool between 0.046875 and 0.109375 seconds (with the same 65500+ rows in the used range). Here is the code:

   Function NewLastRow(rTest As Range) As Long
     Dim vTest As Variant
     Dim lTest As Long
     Dim iCol As Long
     Dim iRow As Long
     Dim iMax As Long
     Dim t As Double
   
     t = Timer
   
     With ActiveSheet.UsedRange
       iMax = .Row + .Rows.Count - 1
     End With
   
     vTest = rTest.EntireColumn.Resize(iMax).Value
   
     lTest = 0
   
     For iCol = LBound(vTest, 2) To UBound(vTest, 2)
       For iRow = UBound(vTest, 1) To lTest + 1 Step -1
         If Len(vTest(iRow, iCol)) > 0 Then
           lTest = iRow
           Exit For
         End If
       Next
     Next
   
     NewLastRow = lTest
   
     Debug.Print Timer - t
   End Function

   I didn't comment initially on your code to be critical, just sharing my impressions. Your response and this show the kind of improvement that we can reach by exchanging ideas.

   Since my old code will give the wrong result if there are hidden but filled cells, I will update my code library with this new routine.
4. Peder Schmedling writes:
   January 6, 2009 at 4:11 am

   Jon,
   you are absolutely right. The loop where I loop cell-by-cell has been one of my concerns with this code. During simulations I found the same as you, the function used noticeable time to complete.. I haven't had any suggestions as to how I could fix this, but thanks to you I now have

   I was aware of, and have often used, the technique of loading values from a range into an array, but I very often find that I lack the experience that allows me to implement the technique(s) in practical examples. I'm therefore thrilled to be pointed in the right direction by a Excel heavyweighter as yourself.

   I have one comment to the test you perform within your loop; testing the length of the current array element would not work for a cell containing the formula ="" (an empty string), also testing the length of anything implicitly casts the data type to string (regardless of if it's a Double, Empty, Integer etc.) this is time consuming. My suggestion is to use the IsEmpty function instead, using this function you don't implicitly cast each array element to a different data type. By introducing the IsEmpty function in the NewLastRow function I found a 45% reduction in time used by the function (based on 50 iterations). Considering that this both improves the accuracy of the NewLastRow function as well as performance, the IsEmpty function is probably the way to go..?

   For reference, this is how the If statement looked like when I measured performance:

   If Not IsEmpty(vTest(iRow, iCol)) Then
       lTest = iRow
       Exit For
   End If
   

   Now, one wouldn't want to use the NewLastRow function in all cases as this is slow compared to the .End method when no hidden rows exist; therefore I figured that a compromise between your approach and mine would probably be the best. A function that uses the .End method when applicable and the array technique when necessary, this is what I came up with:

   '******************************************************************************
   '* Purpose   :
   '*  Determines the last used cell for a given column in a given worksheet.
   '* Parameters:
   '*  [In] lngColNum, column number to get last used cell from
   '*  [In] sht, reference to the worksheet to look in (as object)
   '* Return    :
   '*  Long, Row number of the last used cell in column lngColNum. If the function
   '*  fails -1 is returned. If no cells are used the function returns 0.
   '******************************************************************************
   Public Function NewGetLastUsedCellOfColumn(ByVal lngColNum As Long, _
       ByRef sht As Worksheet) As Long
       On Error GoTo ErrorHandler
       Dim lngLastUsedCell As Long
       Dim lngUsedCellCnt As Long
       Dim lngMaxRow As Long
       Dim rng As Excel.Range
       Dim avarVals As Variant
       Dim iavar As Long
   
       With sht
           'Max number of rows
           lngMaxRow = .Rows.Count
   
           'Bottom cell has content?
           If Not IsEmpty(.Cells(lngMaxRow, lngColNum)) Then
               NewGetLastUsedCellOfColumn = lngMaxRow
               GoTo ExitProc:
           End If
   
           'Estimate position of last used cell using .End() method
           lngLastUsedCell = .Cells(lngMaxRow, lngColNum).End(xlUp).Row
   
           'Let Excel attempt to estimate the last used cell
           Set rng = Intersect( _
               .UsedRange, _
               .Range( _
                   .Cells(lngLastUsedCell + 1, lngColNum), _
                   .Cells(lngMaxRow, lngColNum)))
   
           'Excel found cells, check for hidden non-empty cells
           If Not rng Is Nothing Then
               If Application.WorksheetFunction.CountA(rng) > 0 Then
                   If rng.Count = 1 Then
                       'If just one cell the array technique can't be used
                       lngLastUsedCell = lngLastUsedCell + 1
                   Else
                       'Load values into array and loop from the end
                       avarVals = rng.Value
                       For iavar = UBound(avarVals) To LBound(avarVals) Step -1
                           If Not IsEmpty(avarVals(iavar, 1)) Then
                               lngLastUsedCell = lngLastUsedCell + iavar
                               Exit For
                           End If
                       Next iavar
                   End If
               End If
           End If
   
           'Return 0 if no cells are used
           If lngLastUsedCell = 1 Then
               If IsEmpty(.Cells(1, lngColNum)) Then
                   NewGetLastUsedCellOfColumn = 0
                   GoTo ExitProc:
               End If
           End If
       End With
   
       NewGetLastUsedCellOfColumn = lngLastUsedCell
   ExitProc:
       On Error Resume Next
       Set rng = Nothing
       Exit Function
   ErrorHandler:
       NewGetLastUsedCellOfColumn = -1
       Resume ExitProc
   End Function
   

   This can easily be adapted to accept any range as input (as in your examples), but I needed it to be compatible with my older version of the function.

   What do you think? Am I making headway?
5. Jon Peltier writes:
   January 6, 2009 at 6:52 am

   Peder -

   Ha, I forgot IsEmpty works on a variant array. That's better than the string coercion. I get in the habit of LEN() because when looking for an empty string,

     If LEN(string) = 0 Then

   is faster than

     If string = "" Then

   since before VB reads a string's contents it first checks its length. Your other refinements are also good ones. Two heads are better than one.

   Jimmy -

   I hate to say it, but I'm not a fan of this code formatter. The highlighted version comes out double spaced, so I have to scroll a lot to read a long procedure, and the width is restrictively narrow. I won't say my technique is better (i.e., pre tags using a special vba class in the stylesheet).

   Part of my objective for my own blog this year, and it might just take all year, is to switch over to a new theme and check out a couple of other code highlighters. The problem is that WordPress still insists on messing with the content within the vba brackets, as seen by the greater than symbol in Peder's latest procedure. I have found an enhanced version of the tinymce editor, which I have only given a cursory run through.
6. JP writes:
   January 6, 2009 at 7:00 am

   I agree, but it's the best I could find. I used style tags for a while, but it's too tedious to post code. I'll try to use the syntax highlighter that I use on the rest of the site; It'll take all day to change over, but I don't have anything else to do

   Actually I found a plugin that can do search and replace on all the posts, so I just updated the syntax highlighter for all posts in about five minutes.
7. JP writes:
   January 6, 2009 at 9:54 am

   I turned off the "Show Line Numbers" feature in the iG:Syntax Hiliter, and the spacing issue is resolved. I also hacked the CSS and made the colors a bit more in line with the VBIDE. For the time being, there will be two different syntax plugins (one for the posts, another for comments).