I had been needing to find an easy way to determine the last used row in a given column, or possibly the next availble row in a column. I search Google and found numerous examples, which I used for a short period of time. Code such as:
Range("A65536").End(xlup).Select
But that code was not 100% reliable and I wanted, as much as possible, to avoid useless .Select statement to try and optimize my code as best I could. Well, I finally managed to get it down to a single line of code without the use of any .Select statements and which is flexible regardless of the version of Excel being used. See my code below:
Dim iLastRow As Long Dim iNextAvailableRow As Long iLastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row iNextAvailableRow = iLastRow +1
The only thing you need to know to use this code is that the 1 in (Rows.Count, 1) represents the columns to determine the last used row in. Hence 1=Column A, 2=Column B, …
Wednesday, May 18th, 2011, 9:06 pm | 
MS Excel VBA Programming | 
RSS feed
Pinging is disabled. But you can skip to the end and leave a 
response.