Using VBA to open a workbook and find the first blank row

DanR1245

New Member
Joined
May 12, 2004
Messages
27
Hi-

I am trying to write some code in an excel workbook that will open another excel workbook and go to the first blank line. I will then take some fields from the first workbook and insert them in appropriate columns in the second workbook.

I am running into trouble trying to have VBA go to the first blank line in the second workbook that was open using VBA.

Here is the code I have so far. The second workbook is opening but it is not making the first cell in the first blank row the active cell.

Thanks!

........


Private Sub CheckBox134_Click()

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
'xlApp.ScreenUpdating = False
Set xlWB = xlApp.Workbooks.Open("file.xls")
'ActiveWorkbook.Close SaveChanges:=True
ActiveWorkbook.Activate

'Set oExcel = GetObject(, "Excel.Application")
'With x1WB.ActiveSheet

Sheets("file").Activate
Selection.End(xlDown).Select


End Sub
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Re: Using VBA to open a workbook and find the first blank ro

Do you really mean the 1st empty row, or the next row down after the end of data?

Is there a column which will always have content if there is anything at all on the row?
 
Upvote 0
Hi - Welcome to the board

After you open the workbook put this

Sheets("Sheet1").Activate 'Change as needed
Range("A65536").End(xlUp).Offset(1,0).Select
 
Upvote 0
Re: Using VBA to open a workbook and find the first blank ro

Thanks. I get a "subscript out of range" with this code. The workbook opens as it should. Do I need to do something else to make it active?

What is happening is excel is taking me to the first blank row of the initial workbook I have open.
 
Upvote 0
Re: Using VBA to open a workbook and find the first blank ro

Try changing "Sheet1" to your sheetname.
 
Upvote 0
Re: Using VBA to open a workbook and find the first blank ro

Yes, this has already been changed. But for some reason, VBA is not performing this action on the desired excel file. It is going to the first blank line of the file from which I ran the macro, not the opened file.
 
Upvote 0
Re: Using VBA to open a workbook and find the first blank ro

That's the way a macro works. It runs within the worksheet/workbook where is resides. If you want to run it on a different file, you need to reference that file. Try this.

Dim WS as worksheet

Set WS = workbooks("Your FileName").worksheets("Your WorkSheet")

ws.Activate 'Change as needed
ws.Range("A65536").End(xlUp).Offset(1,0).Select
 
Upvote 0
Re: Using VBA to open a workbook and find the first blank ro

Cbrine said:
That's the way a macro works. It runs within the worksheet/workbook where is resides {snip}

Not really. Generally macros will run on the active workbook/worksheet. Not necessarily this workbook.
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top