VBA : Ranges

Pesky Weasel · Apr 29th 2004

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, _
SearchDirection:=xlPrevious).Column

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, _
SearchDirection:=xlPrevious).Row

End If

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

End Sub

Is there a more efficient way of doing this?

Regards
Weasel

yjoshi · Apr 29th 2004

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...

Code

Sub LastCell1()


Dim LastColumn As Integer
Dim LastRow As Long


If WorksheetFunction.CountA(Cells) &gt; 0 Then
'Search for any entry, by searching backwards by Columns.
LastColumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=Cells(1, LastColumn), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
Cells(LastRow, LastColumn).Offset(1, 0).Select


End If
End Sub

Display More

Pesky Weasel · Apr 29th 2004

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
Weasel

Richie(UK) · Apr 29th 2004

Hi PW,

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

Code

Sub Test()
    Dim rngTest As Range
    
    Set rngTest = PWLastCell(Sheet1)
    If Not rngTest Is Nothing Then
        MsgBox "Pesky last cell is : " & rngTest.Address
    End If
End Sub


Function PWLastCell(ws As Worksheet) As Range
    Dim LastColumn As Integer
    Dim LastRow As Long
     
    Set PWLastCell = Nothing
    With ws
        If WorksheetFunction.CountA(.Cells) &gt; 0 Then
            LastColumn = .Cells.Find(What:="*", After:=.Cells(1, 1), _
                SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
            LastRow = .Cells(Rows.Count, LastColumn).End(xlUp).Row
            Set PWLastCell = .Cells(LastRow, LastColumn).Offset(1, 0)
        End If
    End With
    
End Function

Display More

HTH

yjoshi · Apr 29th 2004

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

Code

cells.SpecialCells(xlCellTypeLastCell).offset(1,0).Select

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

HTH (Hope this helps)

yjoshi · Apr 29th 2004

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.

Code

Sub last_cell()
Dim rng As Range
Dim lrow, lcol As Integer
lrow = 1
lcol = 1
If WorksheetFunction.CountA(Cells) &gt; 0 Then
    Set rng = Union(Cells.SpecialCells(xlCellTypeConstants), _
    Cells.SpecialCells(xlCellTypeFormulas))
    For Each cell In rng
        If cell.Row &gt; lrow Then lrow = cell.Row
        If cell.Column &gt; lcol Then lcol = cell.Column
    Next
Cells(lrow + 1, lcol).Select
End If
End Sub

Display More

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

Pesky Weasel · Apr 29th 2004

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
Weasel

Pesky Weasel · Apr 30th 2004

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:

Code

Sub Test()
    Dim rngTest As Range
     
    Set rngTest = PWLastCell(Sheet1)
    If Not rngTest Is Nothing Then
        MsgBox "Pesky last cell is : " & rngTest.Address
    End If
End Sub
 
Function PWLastCell(ws As Worksheet) As Range
    Dim LastColumn As Integer
    Dim LastRow As Long
      
    Set PWLastCell = Nothing
    With ws
        If WorksheetFunction.CountA(.Cells) &gt; 0 Then
            LastColumn = .Cells.Find(What:="*", After:=.Cells(1, 1), _
                SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
            LastRow = WorksheetFunction.CountA(Range(Cells(1, LastColumn), Cells(65536, LastColumn)))
            Set PWLastCell = .Cells(LastRow, LastColumn).Offset(1, 0)
        End If
    End With
     
End Function

Display More

Richie(UK) · Apr 30th 2004

Hi PW,

Make sure, if you keep the code in its current form, that you make the Range and Cells objects reference the sheet variable that has been passed to the function (use the full-stop (.) as in the other line).

VBA : Ranges

Participate now!

Share

Users Viewing This Thread