[FIXED] How to select a dynamic range based on a cell value in Excel with Python

Issue

I am having a hard time trying to find anything relating to my question. All I have found so far is selecting ranges based off of a static range, but unfortunately the data can change from week to week.

There are multiple data blocks with different rows and columns located in the same sheet but have titles above the data. My goal is to find a title i.e. row 36 or 40, move a row down and essentially do a ctrl+down ctrl+right for selecting a range and then creating a table and naming a table based off of the title.

import openpyxl

def tables(title):
    for cell in pws_sheet["A"]: #pws_sheet["A"] will return all cells on the A column until the last one
        if (cell.value is not None): #check if cell is not empty
            if title in cell.value: #check if the value of the cell contains the title
                row_coord = cell.row #put row number into a variable

tables("All Call Distribution by Hour")

I'm currently able to find the row based off of the title, save the title into a variable, but I am lost on figuring out how to select the bottom right of each data block and selecting it as a range and creating the table from that range.

EDIT 1: Title row is correct, end row is the acting like max_row, and the num_cols is showing the cell.values instead of just a single max column for that table.

def find_table(title, sheet):
    title_row = None
    for row in sheet.iter_rows():
        if row[0].value == title:
            #Find the title row
            title_row = row[0].row
        if row[0].value is None and title_row:
            end_row = row[0].row - 1
            num_cols = [cell.value for cell in sheet[title_row+1] if cell.value is not None]
    else:
        #The last row in the sheet
        end_row = row[0].row
    print(f"Row: {title_row}, Column: {num_cols}, End Row: {end_row}")
    return title_row, num_cols, end_row

OUTPUTS: Row: 40, Column: ['Within', '# Calls', '% Calls'], End Row: 138

Solution

For selecting the cells you want, try something like this

def find_table(sheet, title):
    title_row = None
    for row in sheet.iter_rows():
        if row[0].value == title:
            # Find the title row
            title_row = row[0].row
        if row[0].value is None and title_row:
            end_row = row[0].row - 1
            break
    else:
        # The last row in the sheet
        end_row = row[0].row
return title_row, end_row

You can find the specific number of columns, for the given table with;

num_cols = len([cell.value for cell in sheet[title_row+1] if cell.value is not None])

That should give you the start and end rows, and the number of columns. You can then select those cells and use them to "make a table" in whatever form that takes for your specific example.

If you want to select a range of cells using Excels 'A1' style notation, you can always use openpyxl.utils.cell.get_column_letter(idx) to translate a numeric column number into the corresponding letter.

This solution is quite simplistic, and makes some assumptions about the format of your excel sheets, such as that the data always starts in ColumnA, that an empty cell in ColumnA indicates a totally empty row, and that the heading row always follows the title row. You would also probably want to add some error handling - for example, what if the title row is not found? Hopefully this can give you a start in the right direction though, and some ideas to try out.

Answered By - wstk