Find the last empty range in excel

BetT 21 Reputation points
2020-11-04T14:45:56.15+00:00

I want to copy some data from File1 excel file to File2 excel file. However File2 excel file already contains some data in that sheet. I don't want my paste operation to overwrite the already existing data on File2. I want to find the last empty range in File2 which I can use in the paste function. However I cannot find the last empty range in File2. So far I have the below code:` 

$xl = New-Object -ComObject Excel.Application
$xl.Visible = $true
$wb1 = $xl.Workbooks.Open($file)
$wb2 = $xl.Workbooks.Open($secfile)
$w1 = $wb1.Worksheets.Item(1)
$w2 = $wb2.Worksheets.Item(5)
$ud1 = $w1.UsedRange
$ud1.Copy()
$w2.paste()

I have to the same operation on 5000 sheets so looking to do in using a script.

Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,522 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Emily Hua-MSFT 27,641 Reputation points
    2020-11-05T06:00:24.97+00:00

    @BetT ,

    We are mainly responsible for general issues of Office desktop applications, but your needs focus more on code, I would remove the tag of "office-excel-itpro".

    Thanks for your understanding.

    0 comments
  2. Ian Xue 36,751 Reputation points Microsoft Vendor
    2020-11-05T14:51:28.303+00:00

    Hi，

    You could select a cell before pasting. For example, to paste right to the used range, it could be like this 

    $xl = New-Object -ComObject Excel.Application  
$xl.Visible = $true  
$wb1 = $xl.Workbooks.Open($file)  
$wb2 = $xl.Workbooks.Open($secfile)  
$w1 = $wb1.Worksheets.Item(1)  
$w2 = $wb2.Worksheets.Item(5)  
$ud1 = $w1.UsedRange  
  
$w2LastColumn = $w2.UsedRange.Columns.Count  
$ud1.Copy()  
$w2.Cells(1, $w2LastColumn+1).select()  
$w2.paste()

    Best Regards，
    Ian

    ============================================

    If the Answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.