Specify range as only selecting filled cells/ending at empty cell Python

Question

Is there a way, using win32com, to specify that Python only selects/copies/pastes/autofills/etc a range that stops when it reaches an empty cell?

i.e. Range(A1:A%End)

Certainly open to xlrd library suggestions, but my entire script is already using win32com. Thanks for any tips folks!

import win32com.client as win32
excel = win32.gencache.EnsureDispatch('Excel.Application')

source = excel.Workbooks.Open("C:\source")
excel.Range("A:AA").Select()
excel.Selection.Copy()

copy = excel.Workbooks.Open("C:\copy")
excel.Range("E:AE").Select()
excel.Selection.PasteSpecial()

Answer 1

You can get the last non-emtpy cell via

XlDirectionDown = 4
last = wb.Range("A:A").End(XlDirectionDown)
range = wb.Range("A1:A"+str(last))

The XlDirectionDown is an XlDirection enum item (xlDown), you can also get its value from COM by dispatching via EnsureDispatch:

xlApp = win32com.client.gencache.EnsureDispatch('Excel.Application')
import win32com.client.constants as cc
XlDirectionDown = cc.xlDown

First line builds the type library for Excel for win32com, which makes constants available.