XLDelRow

[dmartinez]

Hello,

Newbie here and getting started with Macro Scheduler for the first time and scripting.

I'll get right to it. I want to find all rows that have a value of 0.00 and delete it after its done checking that sheet to then jump to the next sheet and run the same process. This is all I got for now :/

XLOpen>C:\Users\dmartinez\desktop\Sample.xls,1,xlBook

XLGetSheetDims>xlbook,Test,rows,cols

While>XLRow<rows
Let>XLRow=0

XLDelRow>xlbook,Test,%XLRow%
Endwhile

[Dorian (MJT support)]

This should get you started.

I'm making two assumptions.

1. You're looking for the zero in a particular cell/column. A1,A2,A3, etc.
2. You want to loop through each Sheet in that Workbook.

You can download DeleteZero.xlsx from here.

Code: Select all

//Open Excel File (please edit the path accordingly)
XLOpen>D:\DeleteZero.xlsx,1,xlBook

//Get list of Sheets
XLGetSheetNames>xlBook,listSheets
Separate>listSheets,;,arrSheetNames

//Loop through the Sheets
let>SheetLoop=0
repeat>SheetLoop
  let>SheetLoop=SheetLoop+1
  let>rows=0
  let>cols=0
  let>FindAndDeleteLoop=0

  //Get Dims for this sheet
  XLGetSheetDims>xlbook,arrSheetNames_%SheetLoop%,rows,cols

  //Loop through cells in Col A for this Sheet 
  Repeat>FindAndDeleteLoop
    let>FindAndDeleteLoop=FindAndDeleteLoop+1
    xlgetcell>xlbook,arrSheetNames_%SheetLoop%,FindAndDeleteLoop,1,ThisCell

    //If the cell contains 0, delete that row    
    if>ThisCell=0
      XLDelRow>xlbook,arrSheetNames_%SheetLoop%,FindAndDeleteLoop

      //Adjust counters accordingly
      Let>FindAndDeleteLoop=FindAndDeleteLoop-1
      Let>rows=rows-1
    endif
  Until>FindAndDeleteLoop,rows
Until>SheetLoop,arrSheetNames_count

[dmartinez]

Amazing!!! Thank you much its working!!!

[dmartinez]

One last thing, how can I check through multiple columns? For example, below I have the built script you gave me but instead of just having column "2" I want it to go through 4,6 and 8 as well. Thanks again for your help!

Repeat>FindAndDeleteLoop
let>FindAndDeleteLoop=FindAndDeleteLoop+1
xlgetcell>xlbook,arrSheetNames_%SheetLoop%,FindAndDeleteLoop,2,ThisCell

//If the cell contains 0, delete that row
if>ThisCell=0
XLDelRow>xlbook,arrSheetNames_%SheetLoop%,FindAndDeleteLoop

//Adjust counters accordingly
Let>FindAndDeleteLoop=FindAndDeleteLoop-1
Let>rows=rows-1
endif
Until>FindAndDeleteLoop,rows
Until>SheetLoop,arrSheetNames_count

[Dorian (MJT support)]

Here you go :

The first change is to get the values for columns 4,6, and 8 (D, F, and H) in addition to just column 2.

If any one of B,D,F or H contain a zero (B OR D OR F OR H), delete that row:

Code: Select all

//Open Excel File
XLOpen>D:\DeleteZero.xlsx,1,xlBook

//Get list of Sheets
XLGetSheetNames>xlBook,listSheets
Separate>listSheets,;,arrSheetNames

//Loop through the Sheets
let>SheetLoop=0
repeat>SheetLoop
  let>SheetLoop=SheetLoop+1
  let>rows=0
  let>cols=0
  let>FindAndDeleteLoop=0

  //Get Dims for this sheet
  XLGetSheetDims>xlbook,arrSheetNames_%SheetLoop%,rows,cols

  //Loop through cells in Col A for this Sheet 
  Repeat>FindAndDeleteLoop
    let>FindAndDeleteLoop=FindAndDeleteLoop+1
    xlgetcell>xlbook,arrSheetNames_%SheetLoop%,FindAndDeleteLoop,2,ColB
    xlgetcell>xlbook,arrSheetNames_%SheetLoop%,FindAndDeleteLoop,4,ColD
    xlgetcell>xlbook,arrSheetNames_%SheetLoop%,FindAndDeleteLoop,6,ColF
    xlgetcell>xlbook,arrSheetNames_%SheetLoop%,FindAndDeleteLoop,8,ColH

    //If the cells contains 0, delete that row    
    IF>{(%ColB% = 0) OR (%ColD% = 0) OR (%ColF% = 0) OR (%ColH% = 0)}

      XLDelRow>xlbook,arrSheetNames_%SheetLoop%,FindAndDeleteLoop

      //Adjust counters accordingly
      Let>FindAndDeleteLoop=FindAndDeleteLoop-1
      Let>rows=rows-1
    endif
  Until>FindAndDeleteLoop,rows
Until>SheetLoop,arrSheetNames_count

Or do the same thing by adding the four values together and seeing if the result = 0

Code: Select all

//Open Excel File
XLOpen>D:\DeleteZero.xlsx,1,xlBook

//Get list of Sheets
XLGetSheetNames>xlBook,listSheets
Separate>listSheets,;,arrSheetNames

//Loop through the Sheets
let>SheetLoop=0
repeat>SheetLoop
  let>SheetLoop=SheetLoop+1
  let>rows=0
  let>cols=0
  let>FindAndDeleteLoop=0

  //Get Dims for this sheet
  XLGetSheetDims>xlbook,arrSheetNames_%SheetLoop%,rows,cols

  //Loop through cells in Col A for this Sheet 
  Repeat>FindAndDeleteLoop
    let>FindAndDeleteLoop=FindAndDeleteLoop+1
    xlgetcell>xlbook,arrSheetNames_%SheetLoop%,FindAndDeleteLoop,2,ColB
    xlgetcell>xlbook,arrSheetNames_%SheetLoop%,FindAndDeleteLoop,4,ColD
    xlgetcell>xlbook,arrSheetNames_%SheetLoop%,FindAndDeleteLoop,6,ColF
    xlgetcell>xlbook,arrSheetNames_%SheetLoop%,FindAndDeleteLoop,8,ColH
    

    //Add the cell values together
    Let>TotalVal={(%ColB%+%ColD%+%ColF%+%ColH%)}

    //If the total valus is 0, delete that row    
    IF>TotalVal=0

      XLDelRow>xlbook,arrSheetNames_%SheetLoop%,FindAndDeleteLoop

      //Adjust counters accordingly
      Let>FindAndDeleteLoop=FindAndDeleteLoop-1
      Let>rows=rows-1
    endif
  Until>FindAndDeleteLoop,rows
Until>SheetLoop,arrSheetNames_count

If all four contain a zero (B AND D AND F AND H) :

Code: Select all

//Open Excel File
XLOpen>D:\DeleteZero.xlsx,1,xlBook

//Get list of Sheets
XLGetSheetNames>xlBook,listSheets
Separate>listSheets,;,arrSheetNames

//Loop through the Sheets
let>SheetLoop=0
repeat>SheetLoop
  let>SheetLoop=SheetLoop+1
  let>rows=0
  let>cols=0
  let>FindAndDeleteLoop=0

  //Get Dims for this sheet
  XLGetSheetDims>xlbook,arrSheetNames_%SheetLoop%,rows,cols

  //Loop through cells in Col A for this Sheet 
  Repeat>FindAndDeleteLoop
    let>FindAndDeleteLoop=FindAndDeleteLoop+1
    xlgetcell>xlbook,arrSheetNames_%SheetLoop%,FindAndDeleteLoop,2,ColB
    xlgetcell>xlbook,arrSheetNames_%SheetLoop%,FindAndDeleteLoop,4,ColD
    xlgetcell>xlbook,arrSheetNames_%SheetLoop%,FindAndDeleteLoop,6,ColF
    xlgetcell>xlbook,arrSheetNames_%SheetLoop%,FindAndDeleteLoop,8,ColH


    //If the cells contains 0, delete that row    
    IF>{(%ColB% = 0) AND (%ColD% = 0) AND (%ColF% = 0) AND (%ColH% = 0)}

      XLDelRow>xlbook,arrSheetNames_%SheetLoop%,FindAndDeleteLoop

      //Adjust counters accordingly
      Let>FindAndDeleteLoop=FindAndDeleteLoop-1
      Let>rows=rows-1
    endif
  Until>FindAndDeleteLoop,rows
Until>SheetLoop,arrSheetNames_count

I recommend watching Using Macro Scheduler's Debugger to Aid Script Creation and then running through step-by-step. This will give you insight as to what each line accomplishes.

[dmartinez]

Problem solved! Thanks again!