1

I'm trying to calculate the number of used rows in a VBA project in Excel 2013 so that I can fill it with a userform (everytime I press the Save button, it adds a new row to the worksheet). The worksheet also has two header rows, so I don't want them to be overwritten.

This should be accomplished by the following code that gets executed as soon as I press the Save button:

Private Sub Save_Click()

Dim totalRows As Long

totalRows = Daten.Cells(Rows.Count, "A").End(xlUp).Row
If totalRows < 2 Then
totalRows = 2
Else
totalRows = totalRows
End If

...

End Sub

However, when I press the Save button, I get the Error "424" Object Required".

I am really lost here - anyone knows what I'm doing wrong? If you need to know more, please tell me so, because I'd really want to see this work.

Psyshadow
  • 55
  • 7
  • 2
    what is `Daten`? From your snippet it looks like it should be a `Worksheet` object but I can't see it being `Dim`ensioned anywhere. Have you tried `Sheets("Sheet1_orSheetName").Cells...`? –  Feb 04 '14 at 08:08
  • 2
    where have you initialized your `Daten` variable? – Dmitry Pavliv Feb 04 '14 at 08:08
  • Ah yes, it is a worksheet that is called that way. – Psyshadow Feb 04 '14 at 08:09
  • 1
    Agree with mehow and simoco. Also do you really need `totalRows = totalRows`? :) Change your entire if/Endif to `If totalRows < 2 Then totalRows = 2` You may want to see [THIS](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) as well – Siddharth Rout Feb 04 '14 at 08:12
  • @Psyshadow: Please post this as an answer (not a comment or an edit to your question), then accept your own answer. – Jean-François Corbett Feb 04 '14 at 10:02
  • @Jean-FrançoisCorbett I can't do that for the first 8 hours after asking but I will do so as soon as I can. – Psyshadow Feb 04 '14 at 13:34

1 Answers1

1

This seems to do the trick (as suggested in the comments)

Dim totalRows As Long

totalRows = Sheets("Daten").Cells(Rows.Count, "A").End(xlUp).Row
If totalRows < 2 Then
totalRows = 2
End If
Psyshadow
  • 55
  • 7