How do I find the last used row in Excel

So I saw a question on the Posh board yesterday and being a wise guy, I had to answer it.
 
Q:

I know how to open spreadsheet via powershell, question is whether there is
a way to determine the rows that contain data

Eg  I coded row less than 5000

for($row = 2; $row -lt 5000; $row++)
{

## i m looking at cell 1, so if cell 1 on the next row is empty, i will quit
the program
 $computername = $ws.Cells.Item($row,1).text
 if ($computername.length -eq 0) {exit}

}

 Is there a way to define number of rows rather than i put a number of 5000
or any other number  ?

 
My Answer:
 
You could try selecting the Active Range. That is all the Rows and
columns that have Data in them;
Then you go to the last used row;

In Powershell;

<—— PS Script ———————————> 

$xlCellTypeLastCell = 11
$xl = New-Object -c excel.application
$wb = $xl.Workbooks.Open("C:ScriptsBook5.xlsx")  #<– Your spreadsheet name goes here
$sh = $wb.worksheets.item(‘Sheet1’) #<– the sheet number or name goes here.
$xl.visible = $true
$used = $sh.usedRange
$lastCell = $used.SpecialCells($xlCellTypeLastCell)
$newRow = $lastCell.row + 1
$sh.cells.item($newRow, 1).value2 = ‘This is the new last row now’

 <———– End Script ———————————>

 Or in vbScript

<——vbScript ———————————>

Const xlCellTypeLastCell = 11
Set oXL = CreateObject ("Excel.Application")
oXL.Visible = True
oXL.DisplayAlerts = False

Set objWorksheet = book1.Worksheets(1)
Set objRange = objWorksheet.UsedRange
objRange.SpecialCells(xlCellTypeLastCell).Activate
intNewRow = oXL.ActiveCell.Row + 1
strNewCell = "A" &  intNewRow
oXL.Range(strNewCell).Activate

<———– End Script ———————————>
 

 
Advertisements

2 thoughts on “How do I find the last used row in Excel

  1. How do you chose what columns you want to put the data in? The Row works greate but I have 2 sets of data and I want on in column A and the other in column B and I am not seeing anything in the script that says “columns”.

  2. Columns are numbered, just like rows. Column A is one, B is two and so on.
    In Excel is Row, Column. Or R1C1.
    So, if you want to write data to Column A you specify:
    $ws.Cells.Item(1, 1) = “FileName” Which makes Row one Column A equal “FileName”.
    $ws.Cells.Item(1, 2) = “Folder” Which makes Row one Column B equal “Folder”

    # How do I find the last used column number?
    #This works for columns and rows

    $mainRng = $ws.UsedRange.Cells
    $ColCount = $mainRng.Columns.Count
    $RowCount = $mainRng.Rows.Count
    $xRow = $RowCount
    $xCol = $ColCount
    $xCol is the last used Column.

    So $ws.Cells.Item(1, $xCol) = “Last Column”
    Which makes Row one Column ? equal “Last Column”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s