get cell location based on its value using xlwings

Question

I am trying to apply autofilter on a column based on a list of values

Once I filter, I would like to get the column position

For ex: In below screenshot, if my filter value is 1, I want cell location to be B10.

Similarly, when my filter value is 2, I want cell location to be B11.

How can I get this? I was trying something like below

import xlwings as xw
for val in filter_val_list:
    for col in range(1, 1000000):
        if sheet.range((row,col)).value == val:
            print("The Row is: "+str(row)+" and the column is "+str(col))

Answer 1

Check if the row is hidden, if not print the cell address:

import xlwings as xw

path = r"test.xlsx"

with xw.App(visible=False) as app:
    wb = xw.Book(path)
    ws = wb.sheets[0]

    for a_cell in ws.used_range:
        if a_cell.api.EntireRow.Hidden == False:
            print(a_cell.address) # Or use .column if you need the column number.
    wb.close()

If the first row of your sheet is a header row that you do not want to get the cell address of, use used_range[1:,:] instead of used_range. You can also specify a column instead of used_range.