0

I'm creating a VBA application with some forms. When the data is inserted into the Table, Column A calculates a value with a formula. After that I need to copy the resulting value (like paste special, values only) into the adjacent Row Copy Last Row from Column A to Last Row Column B

I just need to know how to select the last row everytime. I have tried with ActiveCell, Find, Range etc. but none are working

Selection.Copy
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues
riskypenguin
  • 2,139
  • 1
  • 10
  • 22
rzb1
  • 41
  • 7
  • `LastRow = Cells(Rows.Count, "A").End(xlUp).Row` this would return the last row for column A in the `ActiveSheet` – Damian Oct 02 '19 at 15:18

3 Answers3

1

Try this:

Selection.Copy
ActiveSheet.Cells(Rows.Count, ActiveCell.Column).End(xlUp).PasteSpecial Paste:=xlPasteValues
riskypenguin
  • 2,139
  • 1
  • 10
  • 22
1

From a programming point of view, it's much better to define variables rather than using Selection, .select, or ActiveCell, etc... This code will place the cell you are looking for into the variable r, assuming the first header row (A1) is not empty. If you don't want to make any assumptions about the first or last row, see the last answer on this page. In the code below, this would mean replacing Set r = r.End(xlDown) with Set r = sh.Range("A:A").Find("*", Range("A1"), SearchDirection:=xlPrevious)

Option Explicit
Sub test()
Dim sh As Worksheet, r As Range
Set sh = ThisWorkbook.Worksheets("Sheet1")
Set r = sh.Range("A1")
Set r = r.End(xlDown)
r.Select 'remove after code has been tested and you know it works
End Sub

If you have more questions, just ask. There's a lot of help available to help you program in the proper way here on StackOverflow.

Tony M
  • 1,694
  • 2
  • 17
  • 33
0

Thanks all, I fixed it with help of both

    LastValue = Range("Table1[Opportunity no.]").End(xlDown).Value
Set ws = Worksheets("Datos")
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row
    With ws
    .Cells(iRow, 2).Value = LastValue
    End With
rzb1
  • 41
  • 7