Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > VBA / Office Integration > Word, PowerPoint, Outlook, and Other Office Products > howto find next empty cell in an excel worksheet?


Reply
 
Thread Tools Display Modes
  #1  
Old 08-12-2002, 02:58 AM
Echo15
Guest
 
Posts: n/a
Question 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!!
Reply With Quote
  #2  
Old 08-12-2002, 05:15 AM
IanPatton IanPatton is offline
Junior Contributor
 
Join Date: Jul 2001
Location: UK
Posts: 216
Default

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
Reply With Quote
  #3  
Old 08-15-2002, 08:48 AM
NateBrei's Avatar
NateBrei NateBrei is offline
Contributor
 
Join Date: Jul 2002
Location: Omaha, NE
Posts: 572
Default

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
Reply With Quote
  #4  
Old 08-15-2002, 12:55 PM
spike2907
Guest
 
Posts: n/a
Default

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
Reply With Quote
  #5  
Old 08-15-2002, 01:53 PM
NateBrei's Avatar
NateBrei NateBrei is offline
Contributor
 
Join Date: Jul 2002
Location: Omaha, NE
Posts: 572
Default

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
Reply With Quote
  #6  
Old 08-15-2002, 06:35 PM
Echo15
Guest
 
Posts: n/a
Default

Thanks everyone!

I'll have to try these out.....
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->