Excel Experts - Stuck Again

[LisaContractorUmbrella]

Sorry guys but I am stuck again. In the middle of a macro and need to be able to place the cursor at the end of a range which will not be constant - any help greatly received. Poss PX accounting advice

[vetran]

Have a look at "ActiveCell" you can set it as well as read it.


You can use the Row and Column property of a Range object. For
example, the following code will print the results to the Immediate
Window.

'This is for the ActiveCell
Debug.Print ActiveCell.Row
Debug.Print ActiveCell.Column

'This is for a specified range location
Debug.Print Range("AA5").Row
Debug.Print Range("AA5").Column

[BoredBloke]

When you say a range, do you mean a named range on the sheet, or the last row/col of the current used range?

If it's the named range you could trap the row and column values using

Row = Range("Range Name").rows.count + range("Range Name").row - 1

Same for column

If its the last used row/col of the sheet then
activesheet.usedrange.rows.count should do the trick.

If all you are doing is trying to find the last row I tend to use

Cells.Find("*",SearchOrder:=xlByRows,SearchDirecti on:=xlPrevious).Row

[eek]

Not an expert but try

Range("A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row).Select

[LisaContractorUmbrella]

Thanks for coming back so quickly guys - think a bit more info will help. Two reports are run each week which will have the same number of columns and a different number of rows - both reports are then exported into Excel and fiddled about with until they are in the format that we need.I then need to amalgamate the data on both reports so that the whole lot can be sorted. I want to do all this with a macro as it is a regular excercise and time consuming. Trouble is I can't find a way of automatically directing the cursor to the last row of the first report so that I can copy and paste the data from the second report directly beneath it so that it can be sorted.

Although I am happy to record macros my knowledge of the code is slightly less than would fit on the average size postage stamp but if I have understood correctly the solutions so far would identify the last cell but not take the cursor there

[LisaContractorUmbrella]

Not an expert but try

Range("A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row).Select

That worked perfectly Thanks Everyone

[BoredBloke]

Do this

set a variable for your row number

dim lLastRow as long

use this to determine the last used row

lLastRow = Cells.Find("*",SearchOrder:=xlByRows,SearchDirecti on:=xlPrevious).Row

You can then use this value in any range selection thing

eg range(cells(2,1),cells(llastrow,5)).select
would select cells(2,1) = A_2 to cells(llastrow,5) = e_LastRow

[LisaContractorUmbrella]

Thanks Tony - this is really useful. Think I am getting there but am having to write the code rather than recording now and it is tricky I have the macro placing the cursor in the correct cell and I have highlighted the area I need from the second worksheet. Trouble is I now don't know how to write the code that will paste it in the area which starts 1 cell down from the last active cell.

[eek]

It gets difficult selecting the cells outside the range as logic is slightly backwards.

From memory its

Range("A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row +1).Select

but its probably better to do something like this as its clearer

NewCell= Cells(Cells.Rows.Count, "A").End(xlUp).Row +1
Range("A" & NewCell).Select

as that is clearer. mind you vba is as clear as mud most of the time.

ActiveSheet.Paste would then paste the contents of the clipboard into the spreadsheet.

[LisaContractorUmbrella]

Hi Eek

Thanks for that - we are now ending up in the right place Trouble is the paste is not working - possibly as the data is on a different worksheet. Macro as it stands at the moment is:

Sub Macro1()
'
' Macro1 Macro
'

'
Columns("F:F").Select
Selection.Cut Destination:=Columns("N:N")
Range("F1").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[8],LEN(RC[8])-2)"
Range("F1").Select
Selection.AutoFill Destination:=Range("F1:F1015"), Type:=xlFillDefault
Range("F1:F1015").Select
Range("A1").Select
Sheets("Daybook Invoices").Select
Range("A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row).Select
NewCell = Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
Range("A" & NewCell).Select
Sheets("Daybook Credits").Select
Range("A1:M1015").Select
ActiveSheet.Paste
End Sub

Comes up with a runtime error 1004 - Paste method of Worksheet class failed. Tricky little blighter!

Any suggestions gratefully received.