Why delete_rows of openpyxl doesn't delete all empty rows

October 9, 2022

I am trying to delete all empty rows in an excel document using openpyxl delete_rows method. And it works fine if there is just one empty row between cells with content, but it would not delete rows if there are more than 2 empty rows. What am I doing wrong?

from openpyxl import *
from openpyxl.utils import get_column_letter
import selenium

wb = load_workbook(r"C:\Users\User\Desktop\реставрация.xlsx")

ws = wb['main']
ws2 = wb['clean']
print(ws[1][1].value)
c = ws2['C5'].value

# for row in range(1, ws.max_row+1):
#     if ws[row][1].value is None:
#         ws2[row][1].value = ws[row][2].value

for row in range(1, ws2.max_row+1):
    if ws2[row][1].value is None:
        ws2.delete_rows(idx=row, amount = amoun)

wb.save(r"C:\Users\User\Desktop\реставрация.xlsx")

enter image description here

>Solution :

The reason for skipping rows is because once you delete a row, the row below it comes up by 1. The range moves one more cell down, so a cell gets skipped. Easiest way to resolve this is to iterate from bottom to top. I have tested it on sample data and it works well. Update the for loop as below. Note that I am not sure what amount=amoun in your code is, but I made it 1.

for row in range(ws2.max_row+1, 1, -1):  ##range is from bottom to top, step -1 
    if ws2[row][1].value is None:
        ws2.delete_rows(idx=row, amount = 1)