What is the syntax in Python Win32com.Client module for Select-Case (excel)

Question

So I'm trying to efficiently delete rows in excel spreadsheets that meet a certain criteria. However, I think that it would probably be faster if I could use some built in VBA functionality that would select all the rows that I want to delete, and then delete them all at once.

The following link is the VBA equivalent of something I'd like to do using the python win32com.client module: https://stackoverflow.com/a/31390697/9453718

I am currently just looping through and deleting rows as I go. I go through all the rows in excel, and if it meets some criteria I call: Sheet.Rows(r).EntireRow.Delete()

Any suggestions?

Edit: here's a sample of what I currently have that deletes each row one by one

import win32com.client as win32

excel = win32.gencache.EnsureDispatch('Excel.Application')
book = excel.Workbooks.Open("ExcelWith200000Lines.xlsm")
sheet = book.Worksheets(0) #gets first sheet

nrows = sheet.UsedRange.Row + sheet.UsedRange.Rows.Count-1 # number of rows
ncols = sheet.UsedRange.Column + sheet.UsedRange.Columns.Count-1 # num of cols

data = sheet.Range('A1:'+NumToLetter(ncols) + str(nrows)).Value #loads all the data on the sheet into the python script

RELEVANT_COL_INDEX=0
for r in range(nrows-1, -1, -1): #starts at last row and goes to first row
    if data[r][RELEVANT_COL_INDEX] == "delete_me":
        sheet.Rows(r+1).EntireRow.Delete() #deletes the row here

book.SaveAs("myOutput.xlsm")

Parfait · Answer 1 · 2019-04-01T19:44:57.870

The SELECT CASE statement is not part of the object library of Excel but is part of the VBA language. When you connect Python as COM interface to Excel using win32com, you are only connecting to Excel and its object library including its objects (workbooks, worksheets, charts, etc.) and their properties and methods.

In fact, VBA does exactly what you are doing in Python: COM interface to the Excel object library. See under Tools \ References and find VBA is usually the first selected, referenced library. Hence it is not part of Excel. The very Select Case docs does not indicate anywhere that is an Excel method. Thus, you can use SELECT CASE in MS Access VBA, Word VBA, Outlook VBA, etc.

Therefore, use the analogous version of VBA's SELECT CASE in Python which likely would be multi-line if and elif statements. While, other languages like Java, PHP, and R maintain the switch method, Python does not have this. See Replacements for switch statement in Python.

Consider below example using your linked Excel question:

if not (xlwsh.Cells(i, 2) in [0.4, 0.045, 0.05, 0.056, 0.063, 0.071, 0.08, 0.09]
        or xlwsh.Cells(i, 2) < 0):

    xlwsh.Rows(i).EntireRow.Delete

What is the syntax in Python Win32com.Client module for Select-Case (excel)

1 Answers1