PowerShell How to delete cell ranges (not rows) in Excel?

Article
2011-06-06

Question

_{Monday, June 6, 2011 11:03 PM}

Hello Everyone:)

I need to know how i can delete certain cell ranges in Excel. Example : Column K Section 1-7 0r Column K & L 1-7

Thank you

All replies (14)

_{Wednesday, June 15, 2011 10:36 PM ✅Answered}

Hi NeoJC,

Don’t worry about asking questions, this forum is for that. Sometimes it is a bit difficult to provide help if there isn’t sample data. I just wrote a demo script that demonstrates the difference between the Shift oprions of the Delete Method. Get it here and run it, I tested on Excel 2010, should work on Excel 2007 but I am not sure about previous versions, if you have problems running it let me know what version of Excel you are running and I’ll try to make it compatible.

The Demo Script will:

1. Create a new workbook

2. Set up ranges on three sheets, two on each

3. Add some color to the ranges to appreciate the outcome

4. Fill some sample data

5. Empty a third of the cells on both ranges, same pattern per sheet

6. Remove the empty cells by using the different Shift direction options

7. Save the workbook as C:\Excel demo - Remove empty cells from range.xlsx

8. Close and exit Excel

Hope this helps you understand how to delete empty cells and how the different Shift options affect the outcome.

Note: To delete the empty cells I traverse the array of cells from the end to the beginning.

Robert Robelo

_{Tuesday, June 7, 2011 12:19 AM | 1 vote}

Here's an example of how you can clear a range of cells or a column using COM

$file = 'C:\Dropbox\My Dropbox\scripts\services.csv'
Get-Service|Export-Csv -Encoding ASCII -NoTypeInformation $file

$app = New-Object -ComObject Excel.Application
$app.visible=$true
$book = $app.Workbooks.Open($file)
$sheet = $book.Worksheets.Item('services')

$range = $sheet.Range("b2","b7")
$range.clear()

$sheet.Columns.item('A').clear()

There is also a delete method you can invoke instead of clear, but that will cause the cells to shift.

http://twitter.com/toenuff
write-host ((0..56)|%{if (($_+1)%3 -eq 0){[char][int]("116111101110117102102064103109097105108046099111109"[($_-2)..$_] -join "")}}) -separator ""

_{Wednesday, June 8, 2011 1:33 AM}

Hi Tome,

Your correct. This approach will only clear the data from the cell range. And if i use the $range.delete() it would mess up data. Is there a way to delete so that the cells shift up?

_{Wednesday, June 8, 2011 4:56 AM}

$range.Delete([Microsoft.office.interop.excel.xldeleteshiftdirection]::xlShiftUp)

Documentation for Range.Delete is here:

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.delete(office.11).aspx

http://twitter.com/toenuff
write-host ((0..56)|%{if (($_+1)%3 -eq 0){[char][int]("116111101110117102102064103109097105108046099111109"[($_-2)..$_] -join "")}}) -separator ""

_{Wednesday, June 8, 2011 11:25 AM}

Thank you...I will try this out

_{Friday, June 10, 2011 12:09 AM}

Hi Tome,

So this function works very well for my system. But what i have found was that the data varies on each desktop. So with that said, the symptom is that if there is more data than expected, it still throws the alignment after i have deleted what the application is spitting out.

So, i was wondering, is there a way that i can just delete all the empty cells in the spreadsheet, then i dont have to worry about how little or alot of data is spit out without miss alignment?

_{Friday, June 10, 2011 5:23 PM}

Just curious, what is the source of this data and what are your exact requirements. I'm wondering if any of this even needs Excel until the very end.http://twitter.com/toenuff
write-host ((0..56)|%{if (($_+1)%3 -eq 0){[char][int]("116111101110117102102064103109097105108046099111109"[($_-2)..$_] -join "")}}) -separator ""

_{Saturday, June 11, 2011 11:20 PM}

The application is internally developed. And unfortunatley i dont manage it, so i am left to clean it up so that it looks pretty.

With that said, do you know of a way to delete all empty cells leaving the data untouched?

_{Sunday, June 12, 2011 11:58 PM}

Anyone?

_{Monday, June 13, 2011 12:17 PM}

well, you'll need to locate the empty cells, so there will need to be some logic behind it so that you can figure out which cells to delete. after quickly skimming this thread, doesn’t look like we have a way to know what to delete. could you show us a sample of the data?

I mean, do you just start at k1, and work down looking at the cells value length and go until you find some value and then delete that range?

_{Monday, June 13, 2011 5:02 PM}

$range.Delete([Microsoft.office.interop.excel.xlCellTypeBlanks.xldeleteshiftdirection]::xlShiftUp)

I tried somethign like this but it gave me an error. Is there an example if i gave a large range that it could look for an empty cells and dlete them and shift them up?

_{Monday, June 13, 2011 5:31 PM}

did you just make that up?

if you look at the link Tome provided

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.delete(office.11).aspx

you'll see that there are only two options xlShiftToLeft or xlShiftUp..
in some of Tome's pervious code you'll see he selects a range of cells by location

$range = $sheet.Range("b2","b7")

that will grab B2 to B7, he then uses the $range.Delete([Microsoft.office.interop.excel.xldeleteshiftdirection]::xlShiftUp) to shift up as you requested. you're new question is, how to specify the range...

so, stop looking at the delete, and work with selecting the correct range from the $sheet, the question is, how do you know the range?

do you always start at B2 and work down until you find something?
if so, then you'll check the contents of B2, and if empty, delete B2 and shift up.

I don’t know the excel stuff that well but

while( $sheet.item("B2") -eq "")
{
$range = $sheet.range("B2","B2")
$range.Delete([Microsoft.office.interop.excel.xldeleteshiftdirection]::xlShiftUp)
}
im not sure if the $sheet.item("b2") is how you'd load the value of the cell, but based on the other code, it should work.

_{Monday, June 13, 2011 6:44 PM}

The fastest way I know is:

· retrieve the cells from the Range

· traverse the array from the end, backwards

· set the current cell to a variable (instead of referencing the Range again when checking its content and deleting it)

· check the cell’s underlying value

o if empty, delete the cell and shift the cell up

This code demonstrates the technique:

** **

# full path to workbook

$file = '<full path>'

# Microsoft.Office.Interop.Excel.XlDeleteShiftDirection

$xlShiftToLeft = -4159

$xlShiftUp = -4162

$xl = New-Object -ComObject Excel.Application -Property @{Visible = $true}

$wb = $xl.Workbooks.Open($file)

# suppose Sheet1 has the range

$sh = $wb.Sheets.Item('Sheet1')

# retrieve all cell, in this case, from the 'Used Range' but works for any range

$cells = $sh.UsedRange.Cells

# traverse the cells backwards, important!

for ($i = $cells.Count; $i -ge 1; $i--) {

** # grab the cell, it is faster this way**

** $cell = $cells.Item($i)**

** # delete cell if its underlying value is null, the empty string or 0**

** if (-not $cell.Value2) {**

** # ensure the cells are shifted up**

** [Void]$cell.Delete($xlShiftUp)**

** }**

}

# ...

# eventually save and close

$wb.Close($true)

$xl.Quit()

# cleanup (get the function at http://wp.me/p15IqD-2Y)

Remove-ComObject

Robert Robelo

_{Wednesday, June 15, 2011 9:40 PM}

Roberto,

This is similar to what i saw in your solution.

I hope this helps anyone. Here is more information on what i have

$Excel = New-Object -Com Excel.Application
$Excel.visible = $false
$Excel.displayalerts = $False
$Excel = $Excel.Workbooks.Add()

$Sheet = $Excel.WorkSheets.Item(1)

here is the range i want to look at

$range = $sheet.range("O200","X200")

I am not sure on the rest.

Share via

PowerShell How to delete cell ranges (not rows) in Excel?

Question

All replies (14)

Additional resources