For more details see LAA Capability Change for Excel To download a tool that shows how much virtual memory is available and how much is being used see Excel Memory Checking Tool Previously, workbooks using large numbers of full column references and multiple worksheets, for example My test on a workbook with 6 million formulas using full column references failed with an Out of Memory message at 4 GB of virtual memory with Excel 2013 LAA and with Excel 2010 but only used 2 GB virtual memory with Excel 2016. There are many different ways of finding End rows but mostly I use as the most fool-proof method (but there are still snags with things like Merged cells).In some circumstances editing Excel Tables where formulas in the workbook use Structured References to the Table could be slow with Excel 2013 and previous versions. The reason this does not work is that Java Script does something called “shallow copying” for objects and arrays.Cells remain flagged as used even when all formatting, values and formulas are removed. Row ' ' last visible cell in Named Range using End(xl Down) ' j Last Visible Range2 = o Sht. As an Excel Office Developer many of the Excel based applications I develop depend on reading data from Excel worksheet ranges, processing it and then writing the processed data back to a worksheet.Two VBA methods for working with the used range are Worksheet. In VBA the way to do this is to read the ranges into variant arrays, loop through the arrays and then write them back.LAA has now been enabled for 32-bit versions of Excel 2013 and Excel 2016, and will minimize out of memory error messages. Row – 1 I was really surprised to find that End(xl UP) worked differently for Tables than for Ranges.LAA doubles available virtual memory from 2GB to 4GB when using 64-bit Windows, and increases available virtual memory from 2GB to 3GB under 32-bit Windows. And with Tables sometimes it seems best to work with a Range that represents the table rather than directly with List Rows: the syntax is not always obvious to me.
Using VBA to get the last row in Excel can be a surprisingly complex task. ‘ ‘ last cell in Named Range ‘ j Last In Range = o Sht. Value2 d Time = Micro Timer For j = LBound(v Arr) To UBound(v Arr) If v Arr(j, 1) = "X" Then If v Arr(j, 2) = "Y" Then n = n 1 End If End If Next j Debug.get Tick Count cy Ticks1 ' Seconds = Ticks (or counts) divided by Frequency If cy Frequency Then Micro Timer = cy Ticks1 / cy Frequency End Function Sub Var Bench() Dim d T1 As Double Dim d T2 As Double Dim d T3 As Double Dim rng1 As Range Dim rng2 As Range Dim var As Variant Dim j As Long Dim var In As Variant Dim var Out() As Variant ' Application. Calculation = xl Calculation Manual var In = This Workbook. Values=rng From.values statement queues a command to write the values back.The next statement await context.sync() executes the 2 commands that are in the queue.Excel 2016 build 16.0.7920.1000 introduced several very useful new worksheet functions: MAXIFS and MINIFS extend the COUNTIFS/SUMIFS family of functions. The task is to count how many rows have an “X” in column A and a “Y” in column B in the same row.These functions have good performance characteristics and should be used to replace equivalent array formulas. Row ‘ ‘ last row in named range current region ‘ j Last In Region = o Sht. This task is much slower than VBA: The invoke Run function loads the values from the range into a proxy range object using context.sync() , then creates a Java Script array (actually an array of arrays) from the proxy object and loops down the array looking for an X and a Y in the same row.The timescale that updates become available to you depends on which update option you are using: For more details on the Office 2016 release cadence names see Slow – Fast Level Names.This post discusses some of the features that have been introduced in Excel 2016 that you can use to improve performance with large or complex workbooks.This led to the perception that Tables should not be used with large numbers of rows. For more details see Why Structured References are Slow in Excel 2013 but fast in Excel 2016. Row – 1 ‘ ‘ last data row in table (excludes total row) ‘ j Last Table Data Row = o Sht. In effect this means that rng2 and rng3 are just different names for rng1: they all refer to the same set of values.The Excel 2016 team studied a number of large workbooks that show slow response when using Filtering, Sorting and Copy/Pasting, and a number of improvements have been made: In Excel 2013 after Filtering or Sorting or Copy/Pasting many rows Excel could be slow responding or would hang. So I need to either create another deep copy to use for rng3 or assign rng1 to rng3.Used Range and the xl Cell Type Last Cell option of Special Cells. Done efficiently this kind of process can be reasonably fast even with large amounts of data.For my test data j Last Used returns 42 because there is some formatting on that row, and xl Cell Type Last Cell returns 39, which is the last visible row before row 42. So how do you do this kind of operation using the Excel JS-API and how does performance compare with VBA?