When some programs export data they will insert #Null! into a cell to indicate there was no data (SPSS to name one). While I would rather have something than nothing, when trying to compute averages or share data with clients the #Null! value is not ideal. 🙁 In this tutorial I demonstrate how easy it is to Replacing Null values in Excel with AutoHotkey.
The following video demonstrates Replacing Null values in Excel. While I didn’t show the entire code in the video, it is below for your reference.
AutoHotkey Function for Replacing Null values in Excel
;***********get handle******************* XL_Handle(XL,1) ; pointer to Application XL_Handle(XL,2) Pointer to Workbook ;***********call function ******************* XL_Replace_Null(XL) return ;***********replace Null******************* XL_Replace_Null(PXL,RG:=""){ if (! RG) RG:=XL_Used_RG(PXL,0) PXL.Range(RG).Replace("#NULL!","") ; } ;***********************Used Range********************************. ;~ XL_Used_RG(XL,Header:=1) ;Use header to include/skip first row XL_Used_RG(PXL,Header=1){ IfEqual,Header,0,Return, XL_First_Col_Alpha(PXL) . XL_First_Row(PXL) ":" XL_Last_Col_Alpha(PXL) . XL_Last_Row(PXL) IfEqual,Header,1,Return, XL_First_Col_Alpha(PXL) . XL_First_Row(PXL)+1 ":" XL_Last_Col_Alpha(PXL) . XL_Last_Row(PXL) } ;***********************First row********************************. ;~ XL_First_Row(XL) XL_First_Row(PXL){ Return, PXL.Application.ActiveSheet.UsedRange.Rows(1).Row } ;***********************Last Row********************************. ;~ XL_Last_Row(XL) XL_Last_Row(PXL){ Return, PXL.Application.ActiveSheet.UsedRange.Rows(PXL.Application.ActiveSheet.UsedRange.Rows.Count).Row } XL_Last_Col_Alpha(PXL){ LastCol:=XL_Last_Col_Nmb(PXL) IfLessOrEqual,LastCol,26, Return, (Chr(64+LastCol)) Else IfGreater,LastCol,26, return, Chr((LastCol-1)/26+64) . Chr(mod((LastCol- 1),26)+65) } XL_Last_Col_Nmb(PXL){ Return, PXL.Application.ActiveSheet.UsedRange.Columns(PXL.Application.ActiveSheet.UsedRange.Columns.Count).Column } ;***********************First Column Alpha**********************************. ;~ XL_Last_Col_Alpha(XL) XL_First_Col_Alpha(PXL){ FirstCol:=PXL.Application.ActiveSheet.UsedRange.Columns(1).Column IfLessOrEqual,LastCol,26, Return, (Chr(64+FirstCol)) Else IfGreater,LastCol,26, return, Chr((FirstCol-1)/26+64) . Chr(mod((FirstCol- 1),26)+65) } ;***********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) }