Find last cell in a spreadshhet using VBA

Hi agasin thanks for the replys sorry i havnt i have been off work. Thaqnks richard but thats not really what i was after

Arlu yes i am needing excel to goto the last cell with a value in instead of going to the last cell with a formula in

I dont think i explained myself as good as i could have sorry

Hope someone can help me

Thanks in advance

How about this?

Dim LastRow As Variant

LastRow = Intersect([A:A], Cells.SpecialCells(xlCellTypeConstants).EntireRow).EntireRow

Originally Posted by JapanDave

How about this?

Dim LastRow As Variant

LastRow = Intersect([A:A], Cells.SpecialCells(xlCellTypeConstants).EntireRow).EntireRow

Thanks but i cant get it to work it does nothing

Originally Posted by Steffen Thomsen

This will ignore formulae and select the last row that has data

Range("A" & Application.WorksheetFunction.CountA(Range("A1:A60000")).Select

Thanks i have copied it into vba and it has a problem

Expected: list separtor or )

So i put a bracket around A like so

Range("A")

But then it gives me another error of Invalid Qualifier and highlights

.CountA

Originally Posted by Steffen Thomsen

Sorry, missing a character, updated a bit

Sheets(1).Range("A" & Application.WorksheetFunction.CountA(Sheets(1).Range("A1:A60000"))).Select

Thanks

For some reason it keeps selecting cell A502

My spreadsheet is 504 rows long and about 50 columns wide and everycell has a forumla to pull the information from all different sheets however it varies day to day how much information goes into it but it never goes past row 504 sometimes there maybe 50 rows worth of data or could be 500 rows of data. This data then needs to be copied into a master spreadsheet to which i do manually but i was trying to make it automatic with vba. The first row of data that gets copied is row 5 and then highlight all the way down to the ast row with data in, like i said it could be row 50 or row 500 but as every row has a formula in i think vba looks at that instead of a value.

Hope this explains it a bit better and i am greatly appericated for all your kind help

Steve

Everycell in

Originally Posted by Steffen Thomsen

Ok, then try this:

uRows = Sheets(1).UsedRange.Rows.Count
Range("A" & uRows - Application.WorksheetFunction.CountBlank(Sheets(1).Range("A" & uRows))).Select

It goes to row 503 which i dont understand as the last row is 504 but in my test spreadsheet the last row with a value that the formula returned is 446 but it skips past that and goes to row 503

Sorry about this

I don't know if it is what you are after, but anyway.

Sub d()

Worksheets("Sheet1").UsedRange.SpecialCells(xlCellTypeFormulas).Copy
Worksheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues


End Sub

Nope none of these are working i dont have a clue what is going on but ill stick to my good old trustworthy copy and paste manually

Thanks for all the hhelp tho people

How about you post a dummy workbook with sensitive info taken out, so we can actually see what you want.

Originally Posted by jeffreybrown

Haven't really followed any of the other suggestions, but try this.

In column A the formula goes down to A17, but the last value is in A5.

Thanks for your input this has half worked, it has given me thwe cell to which i need selecting but i have tried to change the code to select the cell instead of given me a msg box.

I have attached a file to which may help you help me

In this sample the formulas on sheet 1 are looking at sheet 2 but the original looks at many different sheets. But on this sheet 1 i am trying to get vba to select the last cell with a value which is A457 (although its not always A457)

Again thanks for all your help

Here you go.
it will select the last row with data and not a formula.
Please mark the thread as SOLVED if it is

Sub FindLastData()

Dim r, i As Integer
r = Range("A:A").Cells.SpecialCells(xlCellTypeLastCell).Row

For i = r To 1 Step -1

    If VBA.Left(Range("A" & i).Formula, 1) <> "=" Then
        Range("A" & i).Select
        Exit For
    End If
Next

End Sub

Originally Posted by Kelshaer

Here you go.
it will select the last row with data and not a formula.
Please mark the thread as SOLVED if it is

Sub FindLastData()

Dim r, i As Integer
r = Range("A:A").Cells.SpecialCells(xlCellTypeLastCell).Row

For i = r To 1 Step -1

    If VBA.Left(Range("A" & i).Formula, 1) <> "=" Then
        Range("A" & i).Select
        Exit For
    End If
Next

End Sub

This selects cell A1 which contains the column labels which have been hand written and do not include a formula, sorry forgot to put column labels on the sample here is a new one

Sub FindLastData()

Dim r, i As Integer
r = Range("A:A").Cells.SpecialCells(xlCellTypeLastCell).Row

For i = r To 1 Step -1
    
    If Range("A" & i) <> "" Then
        Range("A" & i).Select
        Exit For
    End If
        
Next

End Sub

Thats great thankyou everyone for your support