4th Floor, 4 Tabernacle Street London EC2A 4LU

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!   Function GetPingResult(Host) Dim objPing As Object Dim objStatus As Object Dim Result As String ‘ping the host Set objPing = GetObject(“winmgmts:{impersonationLevel=impersonate}”). _ ExecQuery(“Select * from Win32_PingStatus Where […]

VBA – Remove Blank Rows

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 […]

Picking up Excel Named Variables

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]

Password Protect Excel 2007

Password Protect Excel 2007 To set a password for the file in Excel 2007, click the Office button and choose Save As. Select the file type that you want (probably the first one: Excel Workbook), and that will launch the Save As dialog box. In the lower-left corner is a button labeled Tools. Click that […]

Hiding zero’s in Excel

hide zero values in selected cells Follow this procedure to hide zero values in selected cells. If the value in one of these cells changes to a nonzero value, the format of the value will be similar to the general number format. Select the cells that contain the zero (0) values that you want to […]