In this tutorial I demonstrate an AutoHotkey function I wrote where I clear Excel: Content, Formatting, Hyperlinks, Comments, etc. This can be particularly helpful when you’ve got a file that either has formatting you want (or don’t want) and you want to keep the cell content intact.
Here is the Video demonstrating how to Clear Excel cells
The code below is what I use to Clear Excel data/formatting. Don’t forget to include the “handle” to connect to the current Excel file.
;***********Get handle******************* XL_Handle(XL,1) ; pointer to Application XL_Handle(XL,2) Pointer to Workbook ;***********Call Clear function******************* XL_Clear(XL,RG:="A1:A8",All:=0,Format:=0,Content:=0,Hyperlink:=1,Notes:=0,Outline:=0,Comments:=1) ;0 clears contents but leaves formatting 1 clears both return ;***********Clearning various things in Excel******************* XL_Clear(PXL,RG="",All=0,Format=0,Content=0,Hyperlink=0,Notes=0,Outline=0,Comments=0){ ; https://analysistabs.com/excel-vba/clear-cells-data-range-worksheet/ ;https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-clearcontents-method-excel (All=1)?(PXL.Application.ActiveSheet.Range(RG).Clear) ;clear the range of cells including Formats (Format=1)?(PXL.Application.ActiveSheet.Range(RG).ClearFormats) ;clear Formats but leave data (Content=1)?(PXL.Application.ActiveSheet.Range(RG).ClearContents) ;clear Data but leave Formats (Hyperlink=1)?(PXL.Application.ActiveSheet.Range(RG).ClearHyperlinks) ;clear Hyperlinks but leave formatting & Data (Notes=1)?(PXL.Application.ActiveSheet.Range(RG).ClearNotes) ;clear Notes (Outline=1)?(PXL.Application.ActiveSheet.Range(RG).ClearOutline) ;clear Outline (Comments=1)?(PXL.Application.ActiveSheet.Range(RG).ClearComments) ;clear Comments } ;***********Get Handle******************* XL_Handle(ByRef PXL,Sel){ ControlGet, hwnd, hwnd, , Excel71, ahk_class XLMAIN ;identify the hwnd for Excel IfEqual,Sel,1, Return, PXL:= ObjectFromWindow(hwnd,-16).application ;Handle to Excel Application IfEqual,Sel,2, Return, PXL:= ObjectFromWindow(hwnd,-16).parent ;Handlle to active Workbook IfEqual,Sel,3, Return, PXL:= ObjectFromWindow(hwnd,-16).activesheet ;Handle to Active Worksheet } ;***borrowd & tweaked from Acc.ahk Standard Library*** by Sean Updated by jethrow***************** ObjectFromWindow(hWnd, idObject = -4){ (if Not h)?h:=DllCall("LoadLibrary","Str","oleacc","Ptr") If DllCall("oleacc\AccessibleObjectFromWindow","Ptr",hWnd,"UInt",idObject&=0xFFFFFFFF,"Ptr",-VarSetCapacity(IID,16)+NumPut(idObject==0xFFFFFFF0?0x46000000000000C0:0x719B3800AA000C81,NumPut(idObject==0xFFFFFFF0?0x0000000000020400:0x11CF3C3D618736E0,IID,"Int64"),"Int64"), "Ptr*", pacc)=0 Return ComObjEnwrap(9,pacc,1) }