Function Reference


_Excel_RangeDelete

Deletes one or multiple cells, rows or columns from the specified worksheet

#include <Excel.au3>
_Excel_RangeDelete ( $oWorksheet, $vRange [, $iShift = Default [, $iEntireRowCol = Default]] )

Parameters

$oWorksheet Excel worksheet object
$vRange Range can be a range object, an A1 range (e.g. "A1:B2", "1:2" (row 1 to 2), "D:G" (columns D to G) etc.
$iShift [optional] Specifies which way to shift the cells. Can be xlShiftToLeft or xlShiftUp of the XlDeleteShiftDirection enumeration.
    If set to keyword Default, Excel decides based on the shape of the range (default = keyword Default)
$iEntireRowCol [optional] If set to 1 the entire row is deleted, if set to 2 the entire column is deleted (default = keyword Default = only delete specified range)

Return Value

Success: 1.
Failure: 0 and sets @error.
@error: 1 - $oWorksheet is not an object or not an worksheet object
2 - $vRange is not a valid range. @extended is set to the COM error code
3 - Error occurred when deleting the range. @extended is set to the COM error code

Example

Example 1

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object and open an example workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeDelete Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Extras\_Excel1.xls")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeDelete Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

; *****************************************************************************
; Delete a range with 3 rows and 2 columns on the active worksheet
; and shift the cells up.
; *****************************************************************************
Local $sRange = "J5:K7"
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeDelete Example 1", "Deleting cells " & $sRange & ".")
_Excel_RangeDelete($oWorkbook.ActiveSheet, $sRange, $xlShiftUp)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeDelete Example 1", "Error deleting cells." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

Example 2

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object and open an example workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeDelete Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Extras\_Excel1.xls")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeDelete Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

; *****************************************************************************
; Delete 2 rows (1 and 2) on worksheet 3
; *****************************************************************************
$oWorkbook.Worksheets(3).Activate
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeDelete Example 2", "Deleting rows 1 and 2 on worksheet 3.")
_Excel_RangeDelete($oWorkbook.Worksheets(3), "1:2")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeDelete Example 2", "Error deleting rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended)