Excel VBA : How to increase row count one by one in every button click

Question

Public Sub CommandButton1_Click()
Dim Name As String
Dim Age As Integer
Dim Row As Integer
Dim i As Integer
i = 2
Row = i
If (Sheet1.Range("D10").Value = "") Then
MsgBox ("Enter Value")
Else
Name = Sheet1.Range("D10").Value
Age = Sheet1.Range("D12").Value
Sheet2.Select
Sheet2.Range("A" & Row) = Name
Sheet2.Range("B" & Row) = Age
Sheet1.Range("D10").Value = ""
Sheet1.Range("D12").Value = ""
End If
End Sub

In the above code i want to increase Row count on every button click. Ex : In excel Row sheet1(D10) save into Sheet2(A2) While click on button next time value of D10 should be save into A3.

I can give you the answer but I want you to try it first ;) [THIS](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) will get you started! — Siddharth Rout, Oct 29 '14 at 09:42
Yes i use increment (i = i+1) but after clicking on button value saved in row 2 — user1227493, Oct 29 '14 at 09:54
No. You need to find the last row which has data and then add 1 to it. Find the last row as I have shown in that post :) — Siddharth Rout, Oct 29 '14 at 09:56

score 1 · Answer 1 · edited May 23 '17 at 12:13

1 There is no need to use the variable i. Simply find the last row as shown HERE which has data and add 1 to it for the next available row.

2 Don't declare the row variable as Integer. xl2007+ have 1048576 rows. Use Long.

3 You don't have to use .Select to write to a sheet. Directly perform the operaton by fully qualifying the objects. You may want to see THIS

Is this what you are trying (UNTESTED)?

Public Sub CommandButton1_Click()
    Dim Name As String
    Dim Age As Integer
    Dim lRow As Long

    With Sheet2
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1

        If Len(Trim(Sheet1.Range("D10").Value)) = 0 Then
            MsgBox ("Enter Value")
        Else
            Name = Sheet1.Range("D10").Value
            Age = Sheet1.Range("D12").Value

            .Range("A" & lRow) = Name
            .Range("B" & lRow) = Age

            Sheet1.Range("D10,D12").ClearContents
        End If
    End With
End Sub

score -1 · Answer 2 · answered Oct 29 '14 at 10:09

Another way is using the UsedRange property. It gets the current range of cells with content in an individual worksheet. The exact property to add into your existing code would look like this:

Sheet2.UsedRange.Rows.Count

With only one row filled in Sheet 2, you'll get a count of 1, which wouldn't work with a +1 to the count (it will keep inserting into row 2). Add some content into that first row of Sheet 2, and then try this code:

Public Sub CommandButton1_Click()
Dim Name As String
Dim Age As Integer
Dim Row As Integer
Dim i As Integer

Row = Sheet2.UsedRange.Rows.Count + 1
If (Sheet1.Range("D10").Value = "") Then
MsgBox ("Enter Value")
Else
Name = Sheet1.Range("D10").Value
Age = Sheet1.Range("D12").Value
Sheet2.Select
Sheet2.Range("A" & Row) = Name
Sheet2.Range("B" & Row) = Age
Sheet1.Range("D10").Value = "test"
Sheet1.Range("D12").Value = ""
End If

End Sub

See how you go!

If you must have that first row empty then let's look at adding some extra code to accommodate that.

- 1 for suggesting `usedrange`. Please check the link that I gave above as to why `UsedRange` shouldn't be used. — Siddharth Rout, Oct 29 '14 at 10:13

Excel VBA : How to increase row count one by one in every button click

2 Answers2