1

I found the below code from Here.

With Sheets("Sheet1")
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
    lastrow = .Cells.Find(What:="*", _
                  After:=.Range("A1"), _
                  Lookat:=xlPart, _
                  LookIn:=xlFormulas, _
                  SearchOrder:=xlByRows, _
                  SearchDirection:=xlPrevious, _
                  MatchCase:=False).Row
Else
    lastrow = 1
End If
End With

My reputation is too low so I can't comment and ask there. What or where is the output? I'm not able to bind the code in?

Community
  • 1
  • 1
Swi
  • 125
  • 1
  • 1
  • 14
  • 1
    It is probably best to ask a new question. Commenting on older answers, particularly if asking questions regarding subject matter not covered is generally frowned upon. I did not fully understand what you meant by *'bind the code in'* but have provided a short snippet on how to use the **lastrow** once you have retrieved it. –  Nov 09 '15 at 08:35
  • I have a form and want to dopy a specific part at the the and of an other sheet. and that should go on and on. So i need a code which finds the last row and paste my copied stuff there. With "bind the code in" i ment i'm not able(because i'm not good at VBA scrupting) to write a code with the other code above which works... – Swi Nov 09 '15 at 08:52
  • First you need to decide where you want to paste the data. At the end of a specific column or at the end of the lastrow of that worksheet. You also need to ascertain that the data you want to paste is going to be pasted horizontally or vertically or both. – Siddharth Rout Nov 09 '15 at 09:38

1 Answers1

3

That code gives you the last row of any column within the worksheet's Worksheet.UsedRange property with a value in it; not the last cell with a value in one particular column. The two may be the same thing but are not guaranteed to be the same.

To get the row with the last value in a particular column (e.g. column B) then this would be more appropriate.

With Sheets("Sheet1")
    If Application.WorksheetFunction.CountA(.Columns(2)) <> 0 Then
        lastrow = .Cells(rows.Count, "B").End(xlUp).Row
    Else
        lastrow = 1
    End If
End With

To use this lastrow to set a value in the next cell (first blank), add 1 and use it in the row_num parameter of a Range.Cells property.

With Sheets("Sheet1")
    If Application.WorksheetFunction.CountA(.Columns(2)) <> 0 Then
        lastrow = .Cells(rows.Count, "B").End(xlUp).Row
    Else
        lastrow = 1
    End If
    .Cells(lastrow + 1, "B") = "my new value"
End With
  • Ok your code works perfectly! but have you any idea how i can paste something there? so i want to paste something starting where "my new value" would be? – Swi Nov 09 '15 at 09:27
  • If the value(s) are on the clipboard (having being previously copied) then just `.Cells(lastrow + 1, "B").Paste`. However, typically I would determine the location (e.g. `lastrow + 1`) first and then copy and paste or directly assign values like `.Cells(lastrow + 1, "B") = ListBox_1.Value`. Suggest you edit your question to include much more detail or close this question off by accepting the answer and starting a new question. –  Nov 09 '15 at 09:33
  • made a new question @Jeeped – Swi Nov 09 '15 at 10:06
  • When i use ".Cells(lastrow + 1, "B").Paste" i get an runtime error? why that`? – Swi Nov 09 '15 at 10:18
  • Possibly the interim operations cancelled the copy or you are not pasting to the active worksheet. There is not enough code to determine what the problem is (either here or in the new question). –  Nov 09 '15 at 10:22