Python Forum

I have a worksheet with two tables, after comparing and deleting duplicates from the one table I'm left with a number of empty rows in column H,I & J. I would like to move the cells up after deleting all the duplicates and have come up with this code.

# Define the range of columns to check
columns_to_check = ['H', 'I', 'J']

# Iterate through each column
for column_letter in columns_to_check:
    column_cells = destination_ws[column_letter]
    
    # Start from the second row and iterate to the last row
    for row in range(2, len(column_cells) - 1):
        cell = column_cells[row]
        
        if cell.value is None:
            # Find the next non-empty cell below
            non_empty_cell_below = None
            for below_row in range(row + 1, len(column_cells)):
                below_cell = column_cells[below_row]
                if below_cell.value:
                    non_empty_cell_below = below_cell
                    break
            
            # Move the entire column up
            if non_empty_cell_below:
                for shift_row in range(row, len(column_cells) - 1):
                    shift_cell = column_cells[shift_row]
                    below_shift_cell = column_cells[shift_row + 1]
                    shift_cell.value = below_shift_cell.value
                    below_shift_cell.value = None

no matter how I've tried indenting it, it always have indentation errors

>>> # Define the range of columns to check
>>> columns_to_check = ['H', 'I', 'J']
>>>
>>> # Iterate through each column
>>> for column_letter in columns_to_check:
... column_cells = worksheet[column_letter]
...
>>> # Start from the second row and iterate to the last row
>>> for row in range(2, len(column_cells) - 1):
... cell = column_cells[row]
...
>>> if cell.value is None:
File "<stdin>", line 1
if cell.value is None:
IndentationError: unexpected indent
>>> # Find the next non-empty cell below
>>> non_empty_cell_below = None
File "<stdin>", line 1
non_empty_cell_below = None
IndentationError: unexpected indent
>>> for below_row in range(row + 1, len(column_cells)):
File "<stdin>", line 1
for below_row in range(row + 1, len(column_cells)):
IndentationError: unexpected indent
>>> below_cell = column_cells[below_row]
File "<stdin>", line 1
below_cell = column_cells[below_row]
IndentationError: unexpected indent
>>> if below_cell.value:
File "<stdin>", line 1
if below_cell.value:
IndentationError: unexpected indent
>>> non_empty_cell_below = below_cell
File "<stdin>", line 1
non_empty_cell_below = below_cell
IndentationError: unexpected indent
>>> break
File "<stdin>", line 1
break
IndentationError: unexpected indent
>>>
>>> # Move the entire column up
>>> if non_empty_cell_below:
File "<stdin>", line 1
if non_empty_cell_below:
IndentationError: unexpected indent
>>> # Calculate the number of empty cells between the current cell and the non-empty cell below
>>> empty_cells_count = below_row - row - 1
File "<stdin>", line 1
empty_cells_count = below_row - row - 1
IndentationError: unexpected indent
>>>
>>> # Shift the column up by the number of empty cells
>>> for shift_row in range(row, len(column_cells) - empty_cells_count):
File "<stdin>", line 1
for shift_row in range(row, len(column_cells) - empty_cells_count):
IndentationError: unexpected indent
>>> shift_cell = column_cells[shift_row]
File "<stdin>", line 1
shift_cell = column_cells[shift_row]
IndentationError: unexpected indent
>>> below_shift_cell = column_cells[shift_row + empty_cells_count + 1]
File "<stdin>", line 1
below_shift_cell = column_cells[shift_row + empty_cells_count + 1]
IndentationError: unexpected indent
>>> shift_cell.value = below_shift_cell.value
File "<stdin>", line 1
shift_cell.value = below_shift_cell.value
IndentationError: unexpected indent
>>> below_shift_cell.value = None
File "<stdin>", line 1
below_shift_cell.value = None
IndentationError: unexpected indent
>>>

Or is there a better to move the cells up, I cant delete whole rows as there are other tables in the same rows. the table currently looks like this but the spaces can change depending on duplicates from other tables. I've attached an example spreadsheet.

Why are you entering code in the Python interpreter? Use a text editor or IDE to type code in a file. Save the file with a .py extension. Run the python script from the commandline.

Output:
python filename,py

I don't really understand what you want.

You can enter single lines in Idle, but you can't enter single lines of a for loop.

You must enter the complete for loop.

You can't "move the cells up". You can copy the values in a row and move them to some other cells

With the simple stuff I do, I populate all cells with zero first. Those None values are always a problem.

If you move values up, isn't that a problem? Those values don't belong in those rows??

I looked up the docs for openpyxl.

It says you can "move cells", but I'm pretty sure they mean "move the values in some cells" because it also says it will overwrite any values in the target cells.

Internally, I suppose, all the cells are probably in a 2-dimensional array. You never "move cells", you only move values.

Quote:Moving ranges of cells

You can also move ranges of cells within a worksheet:

Quote:>>> ws.move_range("D4:F10", rows=-1, cols=2)

This will move the cells in the range D4:F10 up one row, and right two columns. The cells will overwrite any existing cells.

If cells contain formulae you can let openpyxl translate these for you, but as this is not always what you want it is disabled by default. Also only the formulae in the cells themselves will be translated. References to the cells from other cells or defined names will not be updated; you can use the Parsing Formulas translator to do this:

Quote:>>> ws.move_range("G4:H10", rows=1, cols=1, translate=True)

This will move the relative references in formulae in the range by one row and one column.

Sounds like you want to move all the empty cells to the end. I would do this by collecting all the non-empty cell values and padding the list to the column length with None. Then you can copy the filtered values back to the column. Like this:

from openpyxl import load_workbook

wb = load_workbook("test.xlsx")
sheet = wb.worksheets[0]
for column in sheet.columns:
    values = [cell.value for cell in column if cell.value is not None]  # Get values for all the non-empty cells
    values = values + [None] * (len(column) - len(values))  # Pad with None to the column length.
    for cell, value in zip(column, values):  # Copy the values to the column.
        cell.value = value