Python - Code to Find Next Empty Row in Excel

/

How to find first empty row in Excel using Python

I often use Excel as a database for Python models. For example I use XLWINGS (python plugin) to grab data from Excel, compute in Python and then put the results back in Excel.

Doing the last part, I often want an easy way to find the first free row in Excel. For example if I have a sheet like this:

python_find_last_row_data.jpg

I would want to add new data to row 15. However the data size varies, so it won’t always be a certain row count.

Here is where my First Empty Row function comes in:

def write_data_to_excel(workbooklocation,sheetname,columnletter):
    import xlwings as xw
    wb = xw.Book(workbooklocation)
    X = wb.sheets[sheetname].range(columnletter + str(wb.sheets[sheetname].cells.last_cell.row)).end('up').row + 1
    cell = columnletter + str(X)
    print(cell)
    return cell

Copy this into a Python script. Use it by running this line:

write_data_to_excel('testing.xlsx','Model Data','A')
#write_data_to_excel('worksheet','sheet','column')

It will return A15 in my example. (My testing.xlsx is in my active directory, if yours is not, just include the full path). To use in action, I would do something like this:

def write_data_to_excel(workbooklocation,sheetname,columnletter):
    import xlwings as xw
    wb = xw.Book(workbooklocation)
    X = wb.sheets[sheetname].range(columnletter + str(wb.sheets[sheetname].cells.last_cell.row)).end('up').row + 1
    cell = columnletter + str(X)
    print(cell)
    return cell

import xlwings as xw
wb = xw.Book('testing.xlsx')
sht = wb.sheets['Model Data']

last_row = write_data_to_excel('testing.xlsx','Model Data','A')

sht.range(last_row).value = ['www.Excellen.com','Free Excel Templates','Free Tutorials','Visit Now']

You import XLWINGS, define workbook and sheet, set last_row as my script and finally add data using the typical sht.range command.

How does it work?

Secret of this code is the cells.last_cell.row and end('up').

Cells.last_cell.row returns the last row of the specified column. End(‘up’) does the same thing as pushing ctrl-up in Excel.

Since creating this code, I worked out this much easier line:

print(xw.Range('A1').end('down').address)

It will return something like $A$23. You can then split using the dollar signs to manipulate to your hearts content! You also can change down, to left, right or up!

Learn more about XLWINGS with my XLWINGS Python overview!