MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

How to decipher next row?

  Asked By: Brooklyn    Date: Nov 05    Category: MS Office    Views: 674

How do I find the next available row in a spreadsheet (and please forgive me
for possibly not using the correct terms in Excel...phuleeze DO correct
me...I'm just learning all this!)

I was contemplating setting up a procedure that would start with say
A1...and hold that info. The next time the user needs to add more info...I
up that to A2...but then I realize that was wrong...it would have to be B1
and letters don't increment as I could do with numbers varMyVar = varMyVar +
1 or however I'd do it.

So if the user needs to add info from a custom dlg into the next row...how
would I go about saying...okay last time the info went into B1...now put it
in C1. Or do I just tell it to go down and find the next empty row???



13 Answers Found

Answer #1    Answered By: Sheryl Morgan     Answered On: Nov 05

There's a MUCH easier way, you'll be glad to hear!

Use this:

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

Answer #2    Answered By: Brian Ross     Answered On: Nov 05

The Range goes to the last cell in the first column ,A65536, and then goes to the top, A1. The Offset part of your code then takes you to the second row  first column.

Range("A1”).Offset(1, 0).Select would do the same thing.

Selection.Offset(1, 0) would take you to the cell below the current cell


Selection.Offset(0,1) would take you to the cell to the right of the current cell.

Answer #3    Answered By: Heidi Larson     Answered On: Nov 05

No, that's not what it does. Try it and see. It goes down to the bottom of Column A, then up only as far as the last cell in Colmn A which contains data, then down one.

Answer #4    Answered By: Nagina Mian     Answered On: Nov 05

You’re right. The documentation for .end(xlup) needs to be a bit clearer.

On my initial test, I used an empty spreadsheet  and always ended up at A2

Answer #5    Answered By: Whitney Cruz     Answered On: Nov 05

>The documentation for ==enter item here=== needs to be a bit clearer

And this surprises you????<eg>

Answer #6    Answered By: Asir Hashmi     Answered On: Nov 05

Okay, I have no idea what all that means...but it looks good!<g>
I'll be messing with this a little later tonight...so thanks. I'll probably
be screaming for help with it later!

Answer #7    Answered By: Saxon Anderson     Answered On: Nov 05

The cells can be addressed like an array.
i2 = i2 + 1
Num1 = ActiveSheet.Cells(i1, i2).Value

Answer #8    Answered By: Geraldine Perkins     Answered On: Nov 05

..now that I can get a grip on...thanks!

Answer #9    Answered By: Corey Jones     Answered On: Nov 05

I forgot to mention, the array is Cells(Rows, Columns)

You can use also use relative addressing

Answer #10    Answered By: Troy Kelley     Answered On: Nov 05

Another option is:
'Move to the Beginning of the Worksheet

'Find the next empty cell in column A
Do Until ActiveCell = ""
ActiveCell.Offset(1, 0).Range("A1").Select

Answer #11    Answered By: Gorkem Yilmaz     Answered On: Nov 05

I've seen all the other answers to this thread, and they all work in terms
of finding the next empty row.

I did want to answer the other problem you were finding...moving 1 column
over. As you found you can't add 1 to the letter and get the desired
result. Adding integers and strings never seems to work right...:-)

The way I move to another column is to activate the cell I want to move from
then use activecell.column + 1 to move over 1.

For example to move from B1 to C1:
Public sub moving()
Dim mycolumn as range

Range("b1").select 'This would not be needed if another part of
your code selected the cell
Mycolumn = range(activecell.column + 1, activecell.row)

End sub

Now that code is untested and off the top of my head, so forgive me any
typos. But hopefully, that answers the column moving part of the question
for you.

Answer #12    Answered By: May Hansen     Answered On: Nov 05

well there are a couple of ways to do this..one would be to test the A# cell for is empty if you are only using the rows... and may delete some info creating "holes" another way that I usually use is the special cells method like this

lCell = worksheets("Sheet1").cells.specialcells(xlcelltypelastcell).row this will give you the row  number of the last row used.

Answer #13    Answered By: Abana Cohen     Answered On: Nov 05

I was concerned that this thread was not long enough, so thought I'd
add my own two cents on an alternative:

A Worksheet object has a property called UsedRange which gives you a
range object representing just the portion of the worksheet which has
data in it. Therefore, another way to determine where to put a new
entry in your worksheet would be:

nextrow = ActiveSheet.UsedRange.Rows.Count + 1
Cells(nextrow, 1).Value = NewEntry

(assuming your data starts in the first row)

The advantage to this method is that if you have data in multiple
columns, you'll find  the last used row  regardless of whether or not
there is anything in column 1. If you only look at column 1 to find
the last row used, you could accidentally overwrite a row that has
data in another column, but not in column 1.

If you're still confused about UsedRange, try entering random values
in random cells, then running this small routine:

Sub SelectUsedRange()
End Sub

Didn't find what you were looking for? Find more on How to decipher next row? Or get search suggestion and latest updates.