CODEWITHSUNDEEP
pythonfor-loopopenpyxl
user1452574
user1452574

Reputation: 485

Best way to iterate through Excel rows and delete based on met criteria

So I have a very simple for loop that deletes rows if a value in column 9 is equal to the employee name:

import openpyxl, os

wb = openpyxl.load_workbook('Opportunities.xlsx', data_only=True)
sheet = wb['OpportunityList']

for rowNum in range(2, sheet.max_row):
    if sheet.cell(row = rowNum, column = 9).value == 'Employee Name':
        print('Deleting row ' + str(rowNum))
        sheet.delete_rows(rowNum)

wb.save('1.xlsx')

The problem I'm having is that let's say row 20 meets the criteria and is deleted, well row 21 now becomes row 20. So the for loop will progress with rowNum 21 and if the row that used to be 21 and is now 20 also meets the criteria, well that row won't be deleted because the for loop already scanned row 20 and can't go back.

How do I solve this problem? Could putting the rows in a list and then deleting them possibly work? Or would that cause the same problem? Thanks in advance

Upvotes: 1

Views: 1994

Answers (1)

Mark
Mark

Reputation: 125

Start from the bottom and work your way up!

for rowNum in range(sheet.max_row, 1, -1):
    if sheet.cell...

Upvotes: 1

Related Questions