Make the VBA script look for the next empty row?

Question

I'm trying to log all of the information processed by a form I have. Each week the form would be updated and I want to keep a log of the data contained in it. Below I have scripted the data being transferred from the form to the Data Log however it is set to a specific row.

Sub DataLog()
'
' DataLog Macro
' Record sales in the data log.
'

'
    Sheets("Cell History").Select
    Range("A7").Select
    ActiveCell.FormulaR1C1 = "=Form!R[-6]C[3]"
    Range("C7").Select
    ActiveCell.FormulaR1C1 = "=Form!RC[2]"
    Range("D7").Select
    ActiveCell.FormulaR1C1 = "=Form!R[1]C[1]"
    Range("E7").Select
    ActiveCell.FormulaR1C1 = "=Form!R[2]C"
    Range("F7").Select
    ActiveCell.FormulaR1C1 = "=Form!R[3]C[-1]"
    Range("G7").Select
    ActiveCell.FormulaR1C1 = "=Form!R[4]C[-2]"
    Range("H7").Select
    ActiveCell.FormulaR1C1 = "=Form!R[5]C[-3]"
    Range("I7").Select
    ActiveCell.FormulaR1C1 = "=Form!R[6]C[-4]"
    Range("J7").Select
    ActiveCell.FormulaR1C1 = "=Form!R[7]C[-5]"
    Range("K7").Select
    ActiveCell.FormulaR1C1 = "=Form!R[8]C[-6]"
    Range("L7").Select
    ActiveCell.FormulaR1C1 = "=Form!R[9]C[-7]"
    Range("N7").Select
    ActiveCell.FormulaR1C1 = "=Form!RC[-3]"
    Range("O7").Select
    ActiveCell.FormulaR1C1 = "=Form!R[1]C[-4]"
    Range("P7").Select
    ActiveCell.FormulaR1C1 = "=Form!R[2]C[-5]"
    Range("Q7").Select
    ActiveCell.FormulaR1C1 = "=Form!R[3]C[-6]"
    Range("R7").Select
    ActiveCell.FormulaR1C1 = "=Form!R[4]C[-7]"
    Range("S7").Select
    ActiveCell.FormulaR1C1 = "=Form!R[5]C[-8]"
    Range("T7").Select
    ActiveCell.FormulaR1C1 = "=Form!R[6]C[-9]"
    Range("U7").Select
    ActiveCell.FormulaR1C1 = "=Form!R[7]C[-10]"
    Range("V7").Select
    ActiveCell.FormulaR1C1 = "=Form!R[8]C[-11]"
    Range("X7").Select
    ActiveCell.FormulaR1C1 = "=Form!R[13]C[-22]"
    Range("Y7").Select
    ActiveCell.FormulaR1C1 = "=Form!R[13]C[-22]"
    Range("Z7").Select
    ActiveCell.FormulaR1C1 = "=Form!R[13]C[-22]"
    Range("AA7").Select
    ActiveCell.FormulaR1C1 = "=Form!R[13]C[-22]"
End Sub

Because I will be updating it weekly, I was wondering how I can make Excel look for the next empty row, starting from row 7. I'm currently using Excel 2013. All help is greatly appreciated, thanks :)

UPDATE:

I used Andy G's code changing it to:

Dim rng As Range
Set rng = Range("A100000").End(xlUp).Offset(1,0)

rng.Value = "orginal cell location"

I removed the second ".Offset(0,1)" as it moved the cell a second time to the right.

Thanks everyone :)

possible duplicate of [Add rows below last row](http://stackoverflow.com/questions/22045353/add-rows-below-last-row) — RubberDuck, May 24 '14 at 17:02
Also see [Avoid Select & Activate](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — RubberDuck, May 24 '14 at 17:05
There are any number of answers here that show how to find the last used row in a worksheet. The problem is you are setting worksheet "Cell History" to formulae. I assume the data in Form is in the same place each week so all you will get in "Cell History" is multiple copies of this week's data. I suspect you want to move values. Again, any number of answers that show you how to do that. — Tony Dallimore, May 24 '14 at 19:53
Possible duplicate of [Error in finding last used cell in VBA](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) — Our Man in Bananas, Jul 10 '18 at 11:18

Andy G · Answer 1 · 2014-05-24T17:40:20.920

3

You can start way down the column, and come up to locate the last cell with a value in it, then go one further down for the next empty cell:

Range("A100000").End(xlUp).Offset(1,0).Select

100000 is an arbitrary number, you can make use of Cells.Rows.Count if you prefer.

But rather than selecting the cell you should store a reference to it and use Offset to insert all the values in the same row:

Dim rng As Range
Set rng = Range("A100000").End(xlUp).Offset(1,0)

rng.Offset(0,1).Value = "whatever"

Selecting cells is very inefficient.

edited May 24 '14 at 17:40

answered May 24 '14 at 17:03

Andy G

19,232
5
47
69

Thanks for the help. I tried implementing that in my script and I'm not sure if I was doing it how you described however, each time I click the button with the assigned macro, the offset component causes it to get the data from the cell below instead of placing it in the box below – Jacob May 25 '14 at 10:15
I've updated the code in my question to show what I have now, thanks again. – Jacob May 25 '14 at 10:35
1

You should not have done that. You have now completely changed the original question, and will confuse anybody who reads this in future. Put it back if you can and add the new information below it, or preferably start a new topic. – Andy G May 25 '14 at 13:23

Make the VBA script look for the next empty row?

1 Answers1