soonyee91 Posted August 1, 2013 Posted August 1, 2013 (edited) 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 Posted August 1, 2013 Posted August 1, 2013 Try it with ="" instead of = nothing. If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag GudeHow to ask questions the smart way! Reveal hidden contents I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from. Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays. - ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script. - Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label. - _FileGetProperty - Retrieve the properties of a file - SciTE Toolbar - A toolbar demo for use with the SciTE editor - GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI. - Latin Square password generator
soonyee91 Posted August 1, 2013 Author Posted August 1, 2013 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 Posted August 1, 2013 Posted August 1, 2013 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 My UDFs and Tutorials: Reveal hidden contents UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
Moderators JLogan3o13 Posted August 1, 2013 Moderators Posted August 1, 2013 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 "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum!
soonyee91 Posted August 2, 2013 Author Posted August 2, 2013 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 Posted August 2, 2013 Posted August 2, 2013 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 My UDFs and Tutorials: Reveal hidden contents UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
Solution soonyee91 Posted August 3, 2013 Author Solution Posted August 3, 2013 (edited) 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 Posted August 3, 2013 Posted August 3, 2013 My UDFs and Tutorials: Reveal hidden contents UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now