how to delete row in excel with python pywin32

Question

Dear, All.

If cells (i,5) does not have value, I want to delete the row in excel with python pywin32 at python 3.6.

this is my code.

def qms(self):
    fname = QtWidgets.QFileDialog.getOpenFileName(self)

    if fname[0]:
        self.Filename.setText(fname[0])
        excel = win32com.client.Dispatch("Excel.Application")
        excel.Visible = True
        f = excel.Workbooks.Open(fname[0])
        fs = f.ActiveSheet
        lastrow = fs.UsedRange.Rows.Count

        for i in range(3, lastrow):
            if not fs.Cells(i,5).Value :
               fs.getCells().deleteRows(i-1,1,True)

        excel.Workbooks.save()
        excel.Quit()

I think there are problem in fs.getCells().deleteRow(i-1,1,True). I cannot understand. could you help me?

Slai · Accepted Answer · 2017-08-09 01:52:40Z

1

for i in range(lastrow, 2, -1):
    if fs.Cells(i,5).Value != ""
        fs.Rows(i).EntireRow.Delete()

or you can probably delete all at once:

fs.UsedRange.Offset(2).Columns(5).SpecialCells(4).EntireRow.Delete()

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-specialcells-method-excel

edited Aug 9, 2017 at 1:52

answered Aug 9, 2017 at 1:41

Slai

23k5 gold badges49 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

박형렬 Over a year ago

thank you for help me. but i can't understand fs.UsedRange.Offset(2).Columns(5).SpecialCells(4).EntireRow.Delete()

박형렬 · Accepted Answer · 2017-08-09 03:39:58Z

0

def qms(self):
    fname = QtWidgets.QFileDialog.getOpenFileName(self)

    if fname[0]:
        self.Filename.setText(fname[0])
        excel = win32com.client.Dispatch("Excel.Application")
        excel.Visible = True
        f = excel.Workbooks.Open(fname[0])
        fs = f.ActiveSheet
        lastrow = fs.UsedRange.Rows.Count

        for i in range(lastrow, 2, -1):
            if fs.Cells(i,5).Value == None or fs.Cells(i,5).Value == "":
                fs.Rows(i).EntireRow.Delete()

answered Aug 9, 2017 at 3:39

박형렬

4071 gold badge10 silver badges25 bronze badges

Collectives™ on Stack Overflow

how to delete row in excel with python pywin32

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related