1

I am trying this code to find out the last row-

msgbox objExcel.ActiveWorkbook.Sheets(1).Range("A" & Rows.Count).EndXL(up).Row

But i am keep getting this error-

Object required: Rows

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
Praveenks
  • 1,436
  • 9
  • 40
  • 79
  • Mattboy has given you the main error. Besides that you have to fully qualify the `Rows` See this link http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba EDIT: Are you doing this from within Excel? I have a feeling that you are using vbscript? – Siddharth Rout Feb 18 '13 at 11:46

3 Answers3

1

Try 

objExcel.ActiveWorkbook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row

It's just your End(xlUp) part that's wrong

mattboy
  • 2,870
  • 5
  • 26
  • 40
  • I tried this code..but now it is giving an error, subscript out of range...what does it mean?I am using vbscript.. – Praveenks Feb 18 '13 at 11:54
  • Hmm... try changing it to `Sheets(1).Rows.Count`. That's what I think Sid means with fully qualifying Rows. – mattboy Feb 18 '13 at 12:01
  • I tried this -objExcel.ActiveWorkbook.Sheets(1).Range("A" & Sheets(1)Rows.Count).End(xlUp).Row..but it is getting an error type mismatch-Sheets – Praveenks Feb 18 '13 at 12:10
  • You're missing a `.` between Sheets(1) and Rows, I don't suppose that's it? Otherwise I'm stumped here. It works fine from Excel, but I'm not so familiar with vbscript, sorry. – mattboy Feb 18 '13 at 12:14
  • still giving the same error...i corrected everything but still getting the same error- object required rows – Praveenks Feb 18 '13 at 12:18
  • @Praveenks: Seems like you missed my question in the above comment. Are you using VBA (from within Excel) or vbscript? – Siddharth Rout Feb 18 '13 at 12:18
  • @Praveenks: Aha... I was expecting your answer below my comment. Anyways, I have posted the answer. – Siddharth Rout Feb 18 '13 at 12:26
0

Is this what you are trying?

Dim oXLApp, wb, ws

'~~> Define xlUp
Const xlUp = -4162

Set oXLApp = CreateObject("Excel.Application")

oXLApp.Visible = True

'~~> Open file. Chnage path as applicable
Set wb = oXLApp.Workbooks.Open("C:\Sample.xlsx")
Set ws = wb.Worksheets(1)

With ws
      MsgBox .Range("A" & .Rows.Count).End(xlUp).Row
End With

Also notice the use of Worksheets rather than Sheets See this link for explanation.

Community
  • 1
  • 1
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
0

This might work:

dim last as integer
ActiveCell.SpecialCells(xlLastCell).Select
last = ActiveCell.SpecialCells(xlLastCell).Row
Tom Fuller
  • 5,291
  • 7
  • 33
  • 42
TheJukiri
  • 1
  • 1