Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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
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)
                Thank you sir! Makes total sense. Your code is briefer and more to the point compared to solution I came up with myself (getting the list of row numbers to delete at first and then loop throught the reverse list).
– Evgenslam
                Oct 9, 2022 at 7:00
Sorry to have bothered you, ladies and gentlemen. Looks like openpyxl has a problem deleting rows when using a loop. I have solved the problem. Looks like you need first to 1)gather rows to be deleted in a list 2)reverse it 3)loop the delete_rows method with the reversed list.
This code solved my problem.
from openpyxl import *
wb = load_workbook(r"C:\Users\User\Desktop\реставрация.xlsx")
ws = wb['main']
ws2 = wb['clean']
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
# gather rows to be deleted in one list
del_rows = []
for row in range(1, ws2.max_row+1):
    if ws2[row][1].value is None:
        del_rows.append(row)
# delete em one by one
for i in reversed(del_rows):
    ws2.delete_rows(i)
wb.save(r"C:\Users\User\Desktop\реставрация.xlsx")
                These are not effective solutions. Try to use the power of an iterator:  iter_rows = sheet.iter_rows(..., , values_only=True); for row in reversed([*iter_rows]): print(any(row), all(row))
– stansy
                Dec 31, 2022 at 17:15