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

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

public sub commandbutton1_click() dim name string dim age integer dim row integer dim integer = 2 row = if (sheet1.range("d10").value = "") 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 above code want increment row count on every button click. ex : in excel row sheet1(d10) save sheet2(a2) while click on button next time value of d10 should save a3.

1 there no need utilize variable i. find lastly row shown here has info , add together 1 next available row.

2 don't declare row variable integer. xl2007+ have 1048576 rows. utilize long.

3 don't have utilize .select write sheet. straight perform operaton qualifying objects. may want see this

is trying (untested)?

public sub commandbutton1_click() dim name string dim age integer dim lrow long sheet2 lrow = .range("a" & .rows.count).end(xlup).row + 1 if len(trim(sheet1.range("d10").value)) = 0 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 end sub

vba excel-vba access-vba