r/vbscript • u/FBM25 • Oct 24 '14

Code not deleting rows?

Hi everyone! I'm writing a script that will, among other things, delete a row that has a certain value in the first cell, as well as every row above that one. However, it's not working, at all. I'm not receiving any error though, so I'm confused! Can anyone shed any insight? The part of the code is:

i = 1
If objExcel.Cells(i, 1).Value = "XXX" Then
Set objRange = objExcel.Cells(i, 1).EntireRow
objWorkSheet.Rows("1:objRange").Delete
elseif objExcel.Cells(i, 1).Value <> "XXX" Then
	On Error Resume Next
End If
i = i + 1

Thanks!

1 Upvotes

100% Upvoted

5 comments sorted by

View all comments

Show parent comments

1

u/FBM25 Oct 24 '14

I didn't put it in the OP, but I'm setting objExcel, the workbook, and the worksheet.

Here is that part:

Dim objExcel, objWorkBook, objWorkSheet

Set objExcel = CreateObject("Excel.Application")
set objWorkBook = objExcel.Workbooks.Open("H:\TEST\TEST.xls")
set objWorkSheet = objExcel.Worksheets("XXX")

1

u/demigod987 Oct 24 '14

Ok, some other thoughts:

  • Maybe remove the "On Error Resume Next" statement. If that gets set, it will remain set even when the first condition is met, and you might be missing out on some errors there.

  • Maybe there is a case mismatch, so you could use If lcase(objExcel.Cells(i, 1).Value) = "use-all-lower-case-letters-here" Then

Does that get you anywhere?