Need help to convert vba code to autoit code

[soonyee91]

This is what I have tried so far

Original vba code:

Sub DDD()
Dim rng As Range, lastRow As Long
Dim lastCol As Long, i As Long
Dim rw As Range, col As Range
Set rng = ActiveSheet.UsedRange

lastRow = rng.Rows(rng.Rows.Count).Row
lastCol = rng.Columns(rng.Columns.Count).Column

For i = lastRow To 1 Step -1
Set rw = Rows(i)
If Intersect(rw, Range("Print_Area")) Is Nothing Then
rw.EntireRow.Delete
End If
Next
For i = lastCol To 1 Step -1
Set col = Columns(i)
If Intersect(col, Range("Print_Area")) Is Nothing Then
col.EntireColumn.Delete
End If
Next
ActiveSheet.UsedRange
End Sub

Autoit Code: (Conversion not completed)

Syntax error on this line: "If $oExcel.Application.intersect($rw, $oExcel.Range("Print_Area")) = nothing Then"

Func _ClearOutsidePrintArea()
    
        Local $rng = $oExcel.Activesheet.UsedRange
        Local $lastrow = $rng.Rows($rng.rows.Count).Row
        Local $lastcol = $rng.Columns($rng.Columns.count).Column

        For $i = $lastrow To 1 Step -1
            Local $rw = $oExcel.rows($i)
            If $oExcel.Application.intersect($rw, $oExcel.Range("Print_Area")) = nothing Then
                $rw.EntireRow.Delete
            EndIf
        Next
        For $i2 = $lastcol To 1 Step -1
            Local $col = $oExcel.columns($i2)
            If $oExcel.Application.intersect($col, $oExcel.Range("Print_Area")) = nothing Then
                $col.EntireRow.Delete
            EndIf
        Next

EndFunc

So far no clue on how to fix the syntax error, any help is appreciated.

Edited August 1, 2013 by soonyee91

[BrewManNH]

Try it with ="" instead of = nothing.

[soonyee91]

BrewManNH,

After set it to "" instead of nothing. The code can runs however the result is not the same as the vba result. I think there is problem in the code.

[water]

Or you could give the latest beta version of AutoIt a try. They support the Null keyword.

If $oExcel.Application.intersect($rw, $oExcel.Range("Print_Area")) = Null Then

[JLogan3o13]

Do all cells/columns in the sheet have data? If so, you can minimize your code by using the following:

$rowCount = $oExcel.Cells.CurrentRegion.Rows.Count
$colCount = $oExcel.Cells.CurrentRegion.Columns.Count

[soonyee91]

Water,

After trying with the beta version that support null, syntax error occur specifically on that line. So i think we still need to use ""

[water]

The Intersect method returns a range object. So could you please try:

$oIntersect = $oExcel.Application.intersect($rw, $oExcel.Range("Print_Area"))
If Not IsObj($oIntersect) Then

[soonyee91]

Water,

Thanks so much!!!! I know the intersect will return range object but never know how to deal with it. Learn a new thing from you!!!!

Problem solved!

The success converted code is as follow: (Hope this help others! This function will delete/hide everything outside of printarea of excel.)

Func _ClearOutsidePrintArea()

    ;place this code in a worksheet loop if you want to perform this function for the every excel sheet

        Local $oPrintarea=$oExcel.Activesheet.PageSetup.PrintArea   ; Obtain print area from the active excel sheet

    If $oPrintarea="" Then      
        $oPrintarea=$oExcel.Activesheet.UsedRange.Address   ; Obtain Default excel print area if user never specify the print area
     Endif 

        Local $rng = $oExcel.Activesheet.UsedRange
        Local $lastrow = $rng.Rows($rng.rows.Count).Row
        Local $lastcol = $rng.Columns($rng.Columns.count).Column

        For $i = $lastrow To 1 Step -1
            Local $rw = $oExcel.rows($i)
            $oIntersect=$oExcel.Application.intersect($rw, $oExcel.Range($oPrintarea))
            If Not isobj($oIntersect) Then
                $rw.EntireRow.Delete   ; change the 'delete' to hide if you want to hide instead of delete
            EndIf
        Next
        For $i2 = $lastcol To 1 Step -1
            Local $col = $oExcel.columns($i2)
            $oIntersect1=$oExcel.Application.intersect($col, $oExcel.Range($oPrintarea))
            If not isobj($oIntersect1) Then
                $col.EntireColumn.Delete   ; change the 'delete' to hide if you want to hide instead of delete
            EndIf
        Next

EndFunc

Edited August 3, 2013 by soonyee91

[water]