Find and remove rows with the word "TRUE" in Excel
in [VbScript]
|
From: OldDog on 14 Apr 2010 16:18 Hi, Here is a bit of code that will delete all the rows that do not have an entry in col "B". intRow = 2 Do Until ws2.Cells(intRow, 1).Value = "" If ws2.Cells(intRow, 2).Value = "" Then 'Delete row range ws2.Rows(intRow & ":" & Row).Delete 'ROW is the last used row defined earlier. intRow = intRow + 1 Else intRow = intRow + 1 End If Loop My next task is to delete all the rows with the word "TRUE" in col "B". If I put: If ws2.Cells(intRow, 2).Value = "TRUE" It deletes every row in the spreadsheet. How do I escape "TRUE" so that my code can find it? TIA; OldDog
From: Pegasus [MVP] on 14 Apr 2010 17:35 "OldDog" <mikef2691(a)comcast.net> wrote in message news:5fe56176-0ff2-450f-afb0-1f318d1565b8(a)i25g2000yqm.googlegroups.com... > Hi, > > Here is a bit of code that will delete all the rows that do not have > an > entry in col "B". > > > intRow = 2 > Do Until ws2.Cells(intRow, 1).Value = "" > If ws2.Cells(intRow, 2).Value = "" Then > 'Delete row range > ws2.Rows(intRow & ":" & Row).Delete 'ROW is the last used row defined > earlier. > intRow = intRow + 1 > Else > intRow = intRow + 1 > End If > Loop > > > My next task is to delete all the rows with the word "TRUE" in col > "B". > > > If I put: > > > If ws2.Cells(intRow, 2).Value = "TRUE" > > > It deletes every row in the spreadsheet. How do I escape "TRUE" so > that my code can find it? > > > TIA; > > > OldDog The code below works as expected. I do not really understand the role of "Row" in your code and I suspect that it is related to your problem. You first check for an empty cell, then you delete a range of cells. You then check for cells containing the string "TRUE", possibly ignoring that you have just deleted a bunch of rows and that you might be examining a different row than the one you think you're examining. I suggest you include the line oExcel.Visible = True in your code, then step through the loop a few times. Do Until WS2.Cells(intRow, 1).Value = "" If WS2.Cells(intRow, 2).Value = "" _ Then WS2.Rows(intRow).Delete If WS2.Cells(intRow, 2).Value = "TRUE" _ Then WS2.Rows(intRow).Delete intRow = intRow + 1 Loop
From: OldDog on 15 Apr 2010 15:28 On Apr 14, 2:35 pm, "Pegasus [MVP]" <n...(a)microsoft.com> wrote: > "OldDog" <mikef2...(a)comcast.net> wrote in message > > news:5fe56176-0ff2-450f-afb0-1f318d1565b8(a)i25g2000yqm.googlegroups.com... > > > > > > > Hi, > > > Here is a bit of code that will delete all the rows that do not have > > an > > entry in col "B". > > > intRow = 2 > > Do Until ws2.Cells(intRow, 1).Value = "" > > If ws2.Cells(intRow, 2).Value = "" Then > > 'Delete row range > > ws2.Rows(intRow & ":" & Row).Delete 'ROW is the last used row defined > > earlier. > > intRow = intRow + 1 > > Else > > intRow = intRow + 1 > > End If > > Loop > > > My next task is to delete all the rows with the word "TRUE" in col > > "B". > > > If I put: > > > If ws2.Cells(intRow, 2).Value = "TRUE" > > > It deletes every row in the spreadsheet. How do I escape "TRUE" so > > that my code can find it? > > > TIA; > > > OldDog > > The code below works as expected. I do not really understand the role of > "Row" in your code and I suspect that it is related to your problem. You > first check for an empty cell, then you delete a range of cells. You then > check for cells containing the string "TRUE", possibly ignoring that you > have just deleted a bunch of rows and that you might be examining a > different row than the one you think you're examining. I suggest you include > the line oExcel.Visible = True in your code, then step through the loop a > few times. > > Do Until WS2.Cells(intRow, 1).Value = "" > If WS2.Cells(intRow, 2).Value = "" _ > Then WS2.Rows(intRow).Delete > If WS2.Cells(intRow, 2).Value = "TRUE" _ > Then WS2.Rows(intRow).Delete > intRow = intRow + 1 > Loop- Hide quoted text - > > - Show quoted text - Thanks, I got it to work soon after I posted this. Sometimes that is what helps, describing the problem to someone else give you the solution. OldDog
|
Pages: 1 Prev: Find and delete the word TRUE in excel Next: HTA - msxml2.xmlhttp.3.0 and trusted sites |