howto find next empty cell in an excel worksheet?

08-12-2002, 02:58 AM

Echo15

Guest

Posts: n/a

howto find next empty cell in an excel worksheet?

Hi!

I'm making this program that writes to an excel file to keep a summary of information. I used the Excel object library as a reference and I can open the excel file.

My problem is how can I find the next empty cell in a column so I know where to append the next information?

Hope someone can help....

Thanks!!

08-12-2002, 05:15 AM

IanPatton

Junior Contributor

Join Date: Jul 2001

Location: UK

Posts: 216

This code finds the next empty cell in the workbook. I don't think its quite what you're looking for but it is a start

Code:

Dim lRealLastRow As Long
Dim lRealLastColumn As Long
Range("A1").Select
On Error Resume Next
lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
lRealLastColumn = Cells.Find("*", Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column
Cells(lRealLastRow + 1, lRealLastColumn).Select

hope this helps

__________________
Regards
Ian

08-15-2002, 08:48 AM

NateBrei

Contributor

Join Date: Jul 2002

Location: Omaha, NE

Posts: 571

Another option. Go to the first populated cell in the column you want and issue the 'End+Down' comparable commmand. That will take you to the last populated cell in that column. Then you just have to move to the next row.

Code:

'Assumes no blank rows within your data
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select

Code:

'If your data contains blank rows within it
Range("A1").Select
Do Until ActiveCell.Row = 65536
    Selection.End(xlDown).Select
Loop
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select

Nate

08-15-2002, 12:55 PM

spike2907

Guest

Posts: n/a

This routine will move down each cell in the column until it finds an empty one. NB: In the statement cells(row,1) The number 1 refers to the column number eg. 'column "A" = 1, "B" = 2 etc.

Row = 1 'start at row 1
Do While Cells(Row,1) < > " "
Cells(Row,1).activate
Row = Row + 1
Loop

Regards Tracey

08-15-2002, 01:53 PM

NateBrei

Contributor

Join Date: Jul 2002

Location: Omaha, NE

Posts: 571

Quote:

Do While Cells(Row,1) < > " "

You are looking here for the cell to contain a space. Using this method, use double-doubles to find a blank cell.

Code:

Do While Cells(Row,1) < > ""

Also, while I've used this method frequently, my earlier posted method is MUCH FASTER for larger sheets (anything over 100 or 200 rows). Once you get to thousands of rows, this method starts taking serious time.

Nate

08-15-2002, 06:35 PM

Echo15

Guest

Posts: n/a

Thanks everyone!

I'll have to try these out.....