How to find the next available empty cell in excel worksheet using VB.NET

Experts,

Here is my question (actually a two part question):

I have a worksheet A and I am copying a row (A5:E5) and I have data in the same worksheet until A50:F50. I want to find the next available empty cell (or row) using code (in this example, A51 will be the next available cell). I am using visual studio 2005 and I can't get to run vb code for reasons I don't know. Can someone also explain me why I am unable to run vb 6 code in visual studio 2005. If I have to run vb6 what should I do? Here is the code that I have


    Public Sub CopyLines()
        Dim oExcel As Object
        Dim oBook As Object
        Dim oSheet1, oSheet2 As Object
        Dim strTemp As String


        oExcel = CreateObject("Excel.Application")
        'oExcel.Visible = True
        oBook = oExcel.Workbooks.Open("C:\book1.xls")

        'Add data to cells of the first worksheet in the new workbook.
        oSheet1 = oExcel.Worksheets(1)
        oSheet2 = oExcel.Worksheets(2)
        If chkMultipleRows.CheckState = CheckState.Checked Then
       strTemp = txtBeginRow.Text & txtBeginColumn.Text & ":" & txtEndRow.Text &_  
                         txtEndColumn.Text
        Else
     strTemp = txtBeginRow.Text & txtBeginColumn.Text & ":" & txtEndRow.Text &_
                         txtEndColumn.Text 'txtBeginRow.Text & txtBeginColumn.Text
        End If

        oSheet1.Range(strTemp).Copy()
        oSheet2.Activate()
        oSheet2.Range("B2").Select()
        oSheet2.Paste()
        'oSheet2.Range("B20").Value2 = oSheet1.Range("A1:G5").Value2
 


        'Save the Workbook and quit Excel.
        oBook.Save()
        rng1 = Cells.Find(What:=*, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
     

        oSheet1 = Nothing
        oSheet2 = Nothing
        oBook = Nothing
        oExcel.Quit()
        oExcel = Nothing
        GC.Collect()
End Sub

I've added all the needed references! I am using Office 2007 but my excel file has been saved as .xls (in stead of .xlsx)
DoofuSAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cyberkiwiCommented:
With the move to .NET, Visual Basic was reinvented as a new language, it looks like VB of old, but it is not! There are things like strong typing that tries to help prevent coding errors. So you cannot run VB6 code.

If you know that the Column A will not have any empty cells, then you can use

lastRowWithData = oSheet1.Range("a1").End(-4121).Row

otherwise, if you trust Excel, this will give you the row that you would end up with if you pressed Ctrl-End in Excel itself

lastRowWithData = oSheet1.UsedRange.Rows.Count
0
DoofuSAuthor Commented:
Nope, there is no assumption that Column A has no empty cells. Most likely, there will be empty cells - just dont know where and that is where the code is needed.


To your second option, can you be more specific? Does that code actually give me the next available cell?
0
cyberkiwiCommented:
The following will return a Range that is A51 (1 cell only in column A) if A50 is the last column in A that is not empty

oSheet1.Range("A"&(oSheet1.UsedRange.Rows.Count+1)).End(-4162).Offset(1)

However, if B61 has some data and is the last row with data _in any column_, then the next one puts you in A62

oSheet1.Range("A"&(oSheet1.UsedRange.Rows.Count+1))
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

cyberkiwiCommented:
Correction - this fixes the case where the first row or more rows are empty

The following will return a Range that is A51 (1 cell only in column A) if A50 is the last column in A that is not empty

oSheet1.Range("A"&(oSheet1.UsedRange.Row + oSheet1.UsedRange.Rows.Count)).End(-4162).Offset(1)

However, if B61 has some data and is the last row with data _in any column_, then the next one puts you in A62

oSheet1.Range("A"&(oSheet1.UsedRange.Row + oSheet1.UsedRange.Rows.Count))
0
DoofuSAuthor Commented:
I will check your code tomorrow and let you know the result. It is midnight in the East coast (of US) :).
0
cyberkiwiCommented:
On the machine where this fails, try to record a macro and do that action again - that is, save to PDF.
Check that it is actually possible and that the syntax is exactly the same (version of Word)
0
DoofuSAuthor Commented:
OK - it continually says COM Exception was unhandled and Member not found. I currently don't have internet on the machine that I run the code in - so Ican't attach a snapshot. Also, why am I not getting any events or members in the context menu. Like when I put a dot after oSheet1 it doesn't show "Range" in the context menu. Am I missing something here>?
0
cyberkiwiCommented:
>> Also, why am I not getting any events or members in the context menu. Like when I put a dot after oSheet1 it doesn't show "Range" in the context menu. Am I missing something here>?

That is because when it doesn't recognize the type.  To get the helpful code completion, you can Dim a variable for it specifically, e.g.

Dim s as Worksheet
s = Workbook.Sheets(1)
s.  <<< will give you help, because it knows what s is

Workbook.Sheets(1).  <<< nope
0
DoofuSAuthor Commented:
Sadly, I can't get either of the below code working. Can you please see what is wrong? I am attaching the error I am getting.

oSheet1.Range("A"&(oSheet1.UsedRange.Row + oSheet1.UsedRange.Rows.Count)).End(-4162).Offset(1)

OR

oSheet1.Range("A"&(oSheet1.UsedRange.Row + oSheet1.UsedRange.Rows.Count))

Error.JPG
0
cyberkiwiCommented:
In your code, Dim oSheet1 and 2 as just Object

Then, access the worksheets using oBook, not oExcel

oSheet1 = oBook.Worksheets(1)
oSheet2 = oBook.Worksheets(1)

Other than that I can't see anything else wrong in there.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DoofuSAuthor Commented:
Thanks for your answers!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.