Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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???

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

Use this:

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

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

And

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

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.

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

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

And this surprises you????<eg>

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!

The cells can be addressed like an array.

i2 = i2 + 1

Num1 = ActiveSheet.Cells(i1, i2).Value

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

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

You can use also use relative addressing

Another option is:

'Move to the Beginning of the Worksheet

Range("A1").Select

'Find the next empty cell in column A

Do Until ActiveCell = ""

ActiveCell.Offset(1, 0).Range("A1").Select

Loop

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)

Range(mycolumn).select

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.

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.

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()

ActiveSheet.UsedRange.Select

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.

Related Topics:

- Counting the rows If the next row value is same as the previous row
- Delete Part of Row and move next Row Down Up
- Select Next Row
- For Each (If Next) Next
- Move data from a column of row couplets to multiple rows of colum
- Can't get Session Options to stop remembering past sessions on next boot
- Find next value
- Finding and replacing the cell next to a cell with a certain string
- Resume Next
- next open cell in column
- Next Workbook or Previous Workbook
- Populate array value using For Next loop
- Next-Generation Java Middleware
- Next Generation in Applet Java Plug-in Technology
- first day of next year
- Merge a series of rows with the same ID reference to a single row automatically using VBA.
- vba routine for eliminating rows with all zeros from spreadsheet
- Filtering rows by colors
- compare rows
- Specifying conditional formatting for the entire row
- Is it possible to have limited number of rows and columns in a sheet
- Manually creating a row outline with no formula anywhere
- How to Sort a Spreadsheet by Alpha and Keep the Row Hierarchy Intact
- Auto filter at row level
- Need Excel VBA Help with deleting rows