I am trying to get the cell ref. for the last used row of a worksheet. I have copied the code from ref book Excel 2002 VBA but all that this produces is an active cell of A1 whereas the next blank row is at 54.

I do have formulas in column extending beyond this but no text.

The ref book refers to this example (Page 111,112) as catering for this.

Help

Try this ...

The example in the book works ok. But the code doesn't activate the last cell. Try to add this to see which values that have been found:

MsgBox (lRealLastRow & "," & lRealLastColumn) .. or

Cells(lRealLastRow, lRealLastColumn).Select

Im almost sure you'll get the real last cell.

Try this one :-

-----------------------
Regards BrianB
Most problems occur from starting at the wrong place.
Use a cup of coffee to make Windows run faster.
It is easy until you know how.

BriabB's is the only version that works OK except the only problem is that I want to disregard formulas and only find the next
blank column after the last data input.

Have tried to revise all replies but to no avail!!

MACCAS's works OK but takes nearly 5 mins (against 2 secs for the one above) to obtain the correct result I want but sometimes causes Excel to lockup.

Birger is correct but I still get the last row which contains formulas but not text in the 10000's

What I am trying to achieve is to update the range of a pivot table to read the latest information of timesheet entries.

As I am using Groups in the pivot table I can not have blanks otherwise I loose date groups of Qrts etc because as soon as a blank date is part of the pivot table range Qrts disappear and individual dates of ever entry is the only selection.

Therefore I will add the code to a command button so that after input of time sheet details the last Row/column ref. is autoed into the pivot table range and the table is refreshed.

Hope all makes sense.

Code so far below, just the range into the pivot table now fails and last row is last row of formulas not data



Sub GetLastCell()
    Dim LastCell As Range
    Dim last As String




     Sheets("Time Sheet Information ").Select
    '------------------------------
    Lastrow = ActiveSheet.Cells.Find(What:="*", _
    SearchDirection:=xlPrevious, _
    SearchOrder:=xlByRows).Row
    '------------------------------
    Lastcol = ActiveSheet.Cells.Find(What:="*", _
    SearchDirection:=xlPrevious, _
    SearchOrder:=xlByColumns).Column

    '------------------------------
    Set LastCell = ActiveSheet.Cells(Lastrow, Lastcol)
    LastCell.Select

    last = Lastrow
    last = ("R3C1:" + "R" + last + ("C14"))

MsgBox (last)

Selection = last

    Sheets("Sheet1").Select
    Range("C6").Select
' [u]this next line give error 1044 ref. not valid </u>
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
        "'Time Sheet Information '!last"
    ActiveSheet.PivotTables("PivotTable2").PivotCache. Refresh
End Sub

I'm not sure whether you can use this approach but it will give you the row without formulas, found from bottom up. I expect you have a fairly good idea of how the pivot structure is build, so you may change the code to fit your needs:

Sub LastRowWithoutFormula()
Dim NoF, i, r As Integer

Range("A65536").End(xlUp).Select ' Substitute A with valid column-letter
NoF = Selection.Row
Range(NoF & ":" & NoF).Select

    For i = 10 To 1 Step -1
        For Each cell In Selection
            If cell.HasFormula Then
                NoF = NoF - 1
                Range(NoF & ":" & NoF).Select
            Else
                Exit For
            End If
        Next
    Next i

r = ActiveCell.Row
Cells(r, 1).Select

End Sub

Last thread from Birger, you misunderstood, I require the last row with data (text, dates etc) but I have to keep formulas way past the row No.I require so that future data entered is calculated.

I am trying to find the range of data entered to be able to use this for the pivot table range updated by a command button.

Hope this explains

Where's the row of data you want, compared to the rows with formulas?
Is it something like this:

  ...
  data
  data
  formulas ??
  empty

Is it always the same pattern ?

If above is the case, you may search bottom up as shown earlier, skip the line with formulas and reach the row with data.

If you havn't got a working solution, please let me know a little more of the structure.

You could do it with an Excel Array formula in the workbook.

Assuming you have formulas (blank or not) in rows 1 to 1000 and colums A to C. Write the following formula in any other blank cell

To set it up as an array formula you will need to enter the formula by holding Ctrl & Shift and then pressing enter whilst the cursor is blinking in the formula bar

This cell will then always report the last non-blank row in your desired range (NB you may need to run application.calulate in any macro where calculation is turned off)

To extend the tested range either change the 1's and 1000's in my formula to the desired ammounts or if you ant to add more columns you need to add another if statement in with the other columns

Hope this helps
Maccas

NB for more help on array formulas check this out

http://<br /> <a href="http://www.c....htm</a><br />