how can my python code automatically know which row to stop at in Excel

Question

I have an Excel worksheet. The below code is to be applied to every row of data. right now it is specified at 100 rows. The number of rows will change each time I run this code. I have to manually open my script and adjust the number of rows. Is there a way to not have a manual process each time and the relevant number of rows are calculated automatically. I do not have much knowledge in coding. Please help.

import openpyxl
wb = openpyxl.load_workbook('Formal Units.xlsx', data_only=True)
sheet = wb['Sheet1']

last_row = 100  
    data_column = 26
    result_column = 10
    
    for i in range(2, last_row):
        if sheet.cell(row=i, column=data_column).value == "R":
            sheet.cell(row=i, column=result_column).value = "Y"
        else:
            sheet.cell(row=i, column=result_column).value = "N"

wb.save('Formal Units.xlsx')

Answer 1

You can iterate rows with iter_row to get openpyxl Cell objects or just values to get cell values.

In your example:

import openpyxl
wb = openpyxl.load_workbook('Formal Units.xlsx', data_only=True)
sheet = wb['Sheet1']
rows_generator = sheet.values
data_column = 26
result_column = 10
headers = next(rows_generator)  # comment if you don't have header row
for (i, row) in enumerate(rows_generator, start=2):  # if you don't have header row start=1
    sheet.cell(row=i, column=result_column).value = 'Y' if row[data_column-1] == 'R' else 'N'  # -1, because list is 0 indexed
wb.save('Formal Units.xlsx')