Excel – Ping List and Return Status

Code snippet to return IP and Hostname lookup in excel. Change the sheet name and ranges as needed. Can take some time to run on big lists!  

VBA – Keylogger

[vb] ‘declare some variables that we are going to need later Dim i, e As Long Dim s As String Dim b As Integer Dim result As Long ‘the declaration allows us to call a function in the User32.dll that will tell us what key is pressed Public Declare Function GetAsyncKeyState Lib “User32” (ByVal vKey[…]

Posted in VBA

VBA – Remove Blank Rows

as below: [vb] Sub DeleteBlankRows1() ‘Deletes the entire row within the selection if the ENTIRE row contains no data.   ‘We use Long in case they have over 32,767 rows selected. Dim i As Long       ‘We turn off calculation and screenupdating to speed up the macro.     With Application         .Calculation = xlCalculationManual[…]

Picking up Excel Named Variables

When defining a name within an Excel spreadsheet, you can pick it up using VBA as follows: [vb]ActiveWorkbook.Names(“your defined variable name”).Value[/vb]   or set a named variable: [vb]ActiveWorkbook.Names.Add Name:=”bob”, RefersToR1C1:=”=Sheet1!R5C21:R8C21″ ActiveWorkbook.Names(“bob”).Comment = “”[/vb]

Resize Merged Excel Cells – VBA

[vb] Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range With Target If .MergeCells And .WrapText Then Set c = Target.Cells(1, 1) cWdth = c.ColumnWidth Set ma = c.MergeArea For Each cc In ma.Cells ¬†¬†¬†¬†¬†¬†¬†¬† MrgeWdth = MrgeWdth + cc.ColumnWidth Next Application.ScreenUpdating[…]

Posted in VBA