CODEWITHSUNDEEP
pythonexcelpython-3.xrowxlwings
ARAVINTHKUMAR J
ARAVINTHKUMAR J

Reputation: 69

How to find first data row and column of excel in python xlwings

I want to find first data row and column of excel in python xlwings. Below are examples:

enter image description here

Or

enter image description here

Upvotes: 0

Views: 1432

Answers (1)

JvdV
JvdV

Reputation: 75840

I believe the safest to do this would be to use API property and simply use VBA Range.Find method. We can now search for anything using a * wildcard to make sure you find formulas (that could show "") AND values. A simplified example:

import xlwings as xw
app = xw.App(visible=False, add_book=False)
wb = app.books.add()
ws = wb.sheets.active
ws['C2'].value = 'check'
row_cell = ws.api.Cells.Find(What="*", After=ws.api.Cells(ws.api.Rows.Count, ws.api.Columns.Count), LookAt=xw.constants.LookAt.xlPart, LookIn=xw.constants.FindLookIn.xlFormulas, SearchOrder=xw.constants.SearchOrder.xlByRows, SearchDirection=xw.constants.SearchDirection.xlNext, MatchCase=False) 
column_cell = ws.api.Cells.Find(What="*", After=ws.api.Cells(ws.api.Rows.Count, ws.api.Columns.Count), LookAt=xw.constants.LookAt.xlPart, LookIn=xw.constants.FindLookIn.xlFormulas, SearchOrder=xw.constants.SearchOrder.xlByColumns, SearchDirection=xw.constants.SearchDirection.xlNext, MatchCase=False)
print((row_cell.Row, column_cell.Column))

We can use SearchDirection.xlNext and start in the very last Excel cell. This is a recommended way in VBA to reliably find your last used row/column, but can also reliably find your first row/column.

Upvotes: 3

Related Questions