• Afternoon All,

    I am trying to locate the a variation of the last cell used in a spreadsheet.

    Each time I create a new series it goes into the next available column. So what I am trying to locate each time is the first available cell in the last used column.

    I have this code so far (taken from Daves concepts on Ozgrid):

    Sub LastCell()

    Dim LastColumn As Integer
    Dim LastRow As Long

    If WorksheetFunction.CountA(Cells) > 0 Then
    'Search for any entry, by searching backwards by Columns.
    LastColumn = Cells.Find(What:="*", After:=[A1], _
    SearchOrder:=xlByColumns, _

    End If
    If WorksheetFunction.CountA(Cells) > 0 Then
    'Search for any entry, by searching backwards by Rows.
    LastRow = Cells.Find(What:="*", After:=Cells(1, LastColumn), _
    SearchOrder:=xlByRows, _

    End If

    Cells(LastRow, LastColumn).Offset(1, 0).Select

    End Sub

    Is there a more efficient way of doing this?


  • Hi Weasel,
    In your code, you are using exactly same if function twise, which can also be avoided.
    Also, the selection should be inside if loop, as, if the sheed does not have any entry then the code will error out.

    I have made these 2 changes...

    Will try to see if i can find any better way...

    Thanks: ~Yogendra

  • Yogendra,

    Thanks! Yes, the double use was staring me in the face but I've been working on 3 different projects today and I was rushing.

    I was hoping that there would be some one line wonder using the range/.end/xlup/xltoleft combo, but this seems to be the best way so far.

    Good pick up about the error, hadn't considered it for this project as the code was very specific to a certain sheet - but always a good idea to make code nice and generic.

    Thanks for your time

  • Hi PW,

    If you are looking to make the code more generic then how about using a function instead? For example:


  • If you are DEAD-SURE that there are no Blank Cells with formatting, then you can use


    However this will fail, if there is a blank cell outside the text-entered bloks, with some formatting enabled.

    HTH (Hope this helps)

    Thanks: ~Yogendra

  • Yet Another Approach.... ( I know there can be plenty more)

    I am using union of special cells containing constants or formulae (which will exclude the formatted blank cells) and then getting the maximum row and column, adding 1 to row instead of offset.

    But i think, because of the for loop, it may not be extremely time efficient....

    Thanks: ~Yogendra

  • Guys,

    Thanks so much for all the suggestions.

    I had some trouble with my original posting, I thought it worked but it only looked that way, in that it just so happened that the only cell the code seemed to select was the second row of the last used column (and I didnt test past that). The same thing was repeated in Yogendras first sample, and in the last post, no cells were found.

    Richie your function worked perfectly. Bravo!!!

    Thanks again for all your help

  • DOH! Almost perfectly.

    I had to make the changes as below to get what I wanted. Having just read my first post again, I was not clear enough on the requirements, and for that I apologise to all.

    What I am doing is creating series of numbers placed in columns. When I create a new series the neame of that series is placed in the first cell of the next avail column (easy enough). Then values are added 1 by 1 to the next available cell in the last used column.

    Anyhoo - this worked:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!