Followers 0

# Delete range in excel

## 10 posts in this topic

#1 ·  Posted (edited)

Hi everybody,

i'm searching a function to delete the content of a range in excel.

My file has about 10000 columns and 20 rows.

I want to clear the content of cells from column 20 to the end of table in each rows from 5 to 15.

To do this with for/next takes tooooo long.

So is there a short way to mark the range and clear the content?

Thanks for every idea!

Dizzy

Edited by Dizzy

##### Share on other sites

$oExcel = ObjCreate("Excel.Application")$oExcel.Visible = 1
$oExcel.WorkBooks.Open ($filename1)
$oExcel.Range ("D5","F15").Select See the above example - it selects the cells from D5 to F15. Then you can just delete the contents. #### Share this post ##### Link to post ##### Share on other sites $oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 1$oExcel.WorkBooks.Open ($filename1)$oExcel.Range ("D5","F15").Select

See the above example - it selects the cells from D5 to F15.

Then you can just delete the contents.

Works perfect!

2 little questions more?

How can i get the last column of the worksheet with ExcelCOM?

Did i have to read the file into an array to get the last line with:

$array = _ExcelSheetUsedRangeGet($file, 1)
$lastline =$array[3]

I think, there is a faster way in COM ..., right?

And where can i get (see) all posibilities offered with COM?

The commands you take aren't displayed by scite "intellisence" ...

Thanks!

Dizzy

##### Share on other sites

Go to the "Include" folder and checkout the Excel.au3 UDF. Or alternatively go to contents of helpfile and checkout the Excel Management.

Also, if you need to make your own function in Excel, the best way is to record a macro to do whatever you want and the export that code into autoit.

[font="Garamond"]Manjish Naik[/font]Engineer, Global Services - QPSHoneywell Automation India LimitedE-mail - Manjish.Naik@honeywell.com

##### Share on other sites

For question 2:

For the Excel COM you will have to visit the MSDN.

For example:

http://msdn.microsoft.com/de-de/library/microsoft.office.interop.excel.range.select%28office.11%29.aspx

For question 1:

If there is no gaps (empty cells) an easy ways is to do:

$oExcel.Application.ActiveSheet.Range("A1").End(-4121).Select #### Share this post ##### Link to post ##### Share on other sites For question 2: For the Excel COM you will have to visit the MSDN. For example: http://msdn.microsoft.com/de-de/library/microsoft.office.interop.excel.range.select%28office.11%29.aspx For question 1: If there is no gaps (empty cells) an easy ways is to do:$oExcel.Application.ActiveSheet.Range("A1").End(-4121).Select

Hi Juvigy,

There are no gaps in my list, so i can try your code.

Can you explain the -4121?

Dizzy

##### Share on other sites

It is the value of the Excel COM constant - xlDown.Other such constants are:

xlDown -4121: xlToLeft -4159: xlToRight -4161: xlUp -4162

##### Share on other sites

It is the value of the Excel COM constant - xlDown.Other such constants are:

xlDown -4121: xlToLeft -4159: xlToRight -4161: xlUp -4162

Hi Juvigy,

thanks for help. I see, i've to dive into COM

Thx

Dizzy

##### Share on other sites

#9 ·  Posted (edited)

$oExcel = ObjCreate("Excel.Application")$oExcel.Visible = 1
$oExcel.WorkBooks.Open ($filename1)
$oExcel.Range ("D5","F15").Select See the above example - it selects the cells from D5 to F15. Then you can just delete the contents. This code is an example of what I always find. It (per my understanding and experimenting) opens a blank Excel spreadsheet and works from there. My problem is that I have an existing spreadsheet (that may or may not be opened yet) and I need to clear a range before I paste new data into it. I cannot seem to get a way to pass MY spreadsheet to autoit. I have included my current code below (with multiple trys commented out). Can someone help me to see what I am doing wrong? ;need to clear the cells a2:w10001 ;$MyExcel = Objget("Excel.Application")
;$MyExcel = ("Microsoft Excel - Particle.xlsm")$MyExcel = ("Particle.xlsm")
MsgBox(262144, "Testing", "Cell clear algorithm starting...",0)

;WITH $MyExcel.activesheet ;MsgBox(262144, "Testing", "inside the 'with'...",0) ;.range("A2:W10001").select ;.range("A2:W10001").clear ;MsgBox(262144, "Testing", "cells should be clear...",0) ;ENDWITH ;$MyExcel.activesheet.range("A2:W10001").select
;$MyExcel.activesheet.range("A2:W10001").clear ;$oExcel = ObjCreate("Excel.Application")
;$oExcel.Visible = 1 ;$oExcel.WorkBooks.Open ($filename1)$MyExcel.Range ("A2","W10001").clear

MsgBox(262144, "Testing", "finished clearing...",0)
Sleep(1000)

I get this error currently and do not know what to do about it:  "C:UserskscarabiDesktopAspex Project0000Testingtemp.au3" (32) : ==> Variable must be of type "Object".:
$MyExcel.Range ("A2","W10001").clear$MyExcel^ ERROR
Edited by TheScarab

##### Share on other sites

#10 ·  Posted (edited)

i could be wrong, but i am quite sure you will have to create the object regardless.. - This *should* delete the range.. however with something like this, especially since you are saying the workbook may be open... if that is the case it would be simpler to create a VBA macro like this:

sub clearrange()

sheets("Sheetname").activate

range("A2:W10001").select

selection.clearcontents

end sub

Otherwise this will open the book and delete the range:

#include <Excel.au3>

$oExcel = _Excel_Open()$Workbook = _Excel_BookOpen($oExcel,'C:\filepath\to\your\worksheet.xlsm')$range = "A2:W100001"
_Excel_RangeDelete($Workbook.ActiveSheet,$Range, $xlShiftUp) _Excel_Close($oExcel)

Alternatively if you are going to use the VBA macro in your workbook(since i see it is .xlsm anyway and probably a better way of doing it) you could alternatively just open the workbook and call the macro when it is not open using this:

#include <Excel.au3>

$oExcel = _Excel_Open()$Workbook = _Excel_BookOpen($oExcel,'C:\filepath\to\your\worksheet.xlsm')$oExcel.Run("clearrange")
_Excel_Close(\$oExcel)

The helpfile will correctly show you how to structure autoit code for _Excel , its all there.... alternatively you can just use VBA depending on what you require.. for a simple clear contents on 1 workbook im not sure what the benefit of autoit would be except for being able to run the built in macros on demand without actually having the book open or visible .. But! each to their own!