VBS Help for Removing Rows

[Eko]

I am using Excel 2007 and with a .vbs file and I am trying to delete the first 11 rows of data in a CSV, then delete the very last row after the first 11 are deleted. After this the file should be saved in XLXS format which currently does work.

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = false
objExcel.displayalerts=false
srccsvfile = objFile
Set objWorkbook = objExcel.Workbooks.open(srccsvfile)
Set objWorksheet1 = objWorkbook.Worksheets(1)

'Need formatting code here

tgtxlsfile = "c:\file.xlsx"
objWorksheet1.SaveAs tgtxlsfile, 51
objExcel.Quit()
Set objWorksheet1 = Nothing
Set objWorkbook = Nothing
Set ObjExcel = Nothing

I have tried the methods below to remove the
first 11 rows but then I do not know how to then select the last used row and then delete that.

I know that if I used

For x = 1 to 11
    Set objRange = objExcel.Cells(i, 1).EntireRow
    objRange.Delete
Next

this should delete the first 11 lines I believe but is there a better way? Thanks and I hope this is in the right
place as its my first post. Hello All!

 

[Trevor G]

Welcome to the Forum,

Try this out to delete Row 1 to 11 and then last row with data in it.

Rows("1:11").Delete Shift:=xlUp
Range("A65000").Select
Selection.End(xlUp).Select
ActiveCell.Offset(-1).EntireRow.Delete

 

[Eko]

I imported what you wrote into my code as below:

Set objExcel = CreateObject("Excel.Application")
            objExcel.Visible = false
            objExcel.displayalerts=false
            srccsvfile = objFile
            Set objWorkbook = objExcel.Workbooks.open(srccsvfile)
            Set objWorksheet1 = objWorkbook.Worksheets(1)
            
            objExcel.Rows("1:11").Delete Shift:=xlUp
            objExcel.Range("A65000").Select
            objExcel.Selection.End(xlUp).Select
            objExcel.ActiveCell.Offset(-1).EntireRow.Delete
            
            tgtxlsfile = "c:\file.xlsx"
            objWorksheet1.SaveAs tgtxlsfile, 51
            objExcel.Quit()
            Set objWorksheet1 = Nothing
            Set objWorkbook = Nothing
            Set ObjExcel = Nothing

except after running it I get "Microsoft VBScript compilation error: Expected statement" error on the line "objExcel.Rows("1:11").Delete Shift:=xlUp". I'm not sure if I'm formatting this wrong or if I'm not addressing the Worksheet in the right way. Please let me know what you think. Thanks!

 

[Trevor G]

Im not a VB Script person, but what would happen if you place the code below the line that saves your file as a xlsx file and before you quit excel, first then see if it treats it like an excel workbook.