asked on

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)

cyberkiwi

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

DoofuS

ASKER

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?

cyberkiwi

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

cyberkiwi

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

DoofuS

ASKER

I will check your code tomorrow and let you know the result. It is midnight in the East coast (of US) :).

cyberkiwi

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)

DoofuS

ASKER

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

cyberkiwi

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

DoofuS

ASKER

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

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DoofuS

ASKER

Thanks for your answers!