Archive for June, 2016
Excel – Get Unique List
June 11th, 2016 | 
Author: Excel – Get Unique List – Method 1
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Sub GetUniques() ' hiker95, 7 / 26 / 2012 ' http://www.mrexcel.com/forum/showthread.php?649576-Extract-unique-values-from-one-column-using-VBA Dim d As Object, c As Variant, i As Long, lr As Long Set d = CreateObject("Scripting.Dictionary") lr = Cells(Rows.Count, 1).End(xlUp).Row c = Range("a2:a" & lr) For i = 1 To UBound(c, 1) d(c(i, 1)) = 1 Next i Range("b2").Resize(d.Count) = Application.Transpose(d.keys) End Sub |
Excel – Get Unique List – Method 2
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
Sub UniqueList() 'http://www.freevbcode.com/ShowCode.asp?ID=4941 Dim rListPaste As Range Dim iReply As Integer On Error Resume Next Set rListPaste = Application.InputBox _ (Prompt:="Please select the destination cell", Type:=8) If rListPaste Is Nothing Then iReply = MsgBox("No range nominated," _ & " terminate", vbYesNo + vbQuestion) If iReply = vbYes Then Exit Sub End If 'May need to specify [NameofSheet].Range, e.g, Sheet1.Range Range("A2", Range("A100000").End(xlUp)).AdvancedFilter _ Action:=xlFilterCopy, CopyToRange:=rListPaste.Cells(1, 1), Unique:=True End Sub |
Excel – Get Unique List – Method 3
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Sub CreateUniqueList() 'http://www.listendata.com/2013/05/excel-3-ways-to-extract-unique-values.html Dim lastrow As Long lastrow = Cells(Rows.Count, "A").End(xlUp).Row ActiveSheet.Range("A2:A" & lastrow).AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=ActiveSheet.Range("B2"), _ Unique:=True End Sub |
Posted in 
Zip with VBS
An interesting post I found via MS forum.
Zip files with 7-ZIP – Method 1
|
1 2 3 4 5 6 7 8 9 10 11 |
Sub ZipIndividualFiles1() Dim file As Variant Const SOURCE = "E:\test\source" Const DEST = "E:\test\destination" Const PATH_TO_7Z = "C:\Program Files\7-Zip\7z.exe" For Each file In CreateObject("Scripting.FileSystemObject").GetFolder(SOURCE).Files Shell PATH_TO_7Z & " a -tzip """ & DEST & "\" & file.Name & ".zip"" """ & file.Path & """" Next End Sub |
Zip files with 7-ZIP – Method 2
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
Sub ZipIndividualFiles2() Dim src As Variant Dim dst As Variant Dim file As Variant Const PATH_TO_7Z = "C:\Program Files\7-Zip\7z.exe" Set src = CreateObject("Shell.Application").BrowseForFolder(0, "Source folder", &H245) If Not src Is Nothing Then src = src.Self.Path & "\" Set dst = CreateObject("Shell.Application").BrowseForFolder(0, "Destination folder", &H245) If Not dst Is Nothing Then dst = dst.Self.Path & "\" For Each file In CreateObject("Scripting.FileSystemObject").GetFolder(src).Files Shell PATH_TO_7Z & " a -tzip """ & dst & file.Name & ".zip"" """ & file.Path & """" Next End If End If End Sub |
Tags:
Remove Last Line From Text
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
Const ForReading = 1 Const ForWriting = 2 Set objFSo = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile("test.txt", ForReading) strContents = objFile.ReadAll objFile.Close arrLines = Split(strContents, vbCrLf) Set objFile = objFSO.OpenTextFile("test.txt", ForWriting) For i = 0 to UBound(arrLines) - 1 objFile.WriteLine arrLines(i) Next Wscript.Echo arrLines(i) & " has been deleted." objFile.Close |
Output Command Prompt Results
|
1 2 3 4 5 6 7 8 9 10 |
Dim ObjExec Dim strFromProc Set objShell = WScript.CreateObject("WScript.Shell") Set ObjExec = objShell.Exec("cmd.exe /c hostname") Do strFromProc = ObjExec.StdOut.ReadLine() 'MsgBox strFromProc WScript.Echo strFromProc Loop While Not ObjExec.Stdout.atEndOfStream |
TeamViewer
I’ve used this software to remotely access friends and family computers to fix issues. They would need to download and install the program as well onto their machine. This software is primarily used for remote administration and web conferencing.
MilkyTracker
If you know what chiptunes are and like them, this program will allow you to make your own. And if you don’t know what it is, think along the line of those old school arcade background music. My favorite has to be “Time Flies by Maktone”.
TSearch
I’ve tested this little freeware tool to manipulate game memory – to boost life, money, ammo, change your score, etc., during single-player gameplay. There’s CheatEngine which is the more widely used and popular memory editor – has more features and comes with a tutorial for starters. Be sure to abide with ToS of games and EULA to avoid getting your game account banned and/or legal issues.
I do want to enforce my disclaimer on this site – you’re downloading at your own risk and are fully responsible of your own actions.
HiJackThis
I’ve used this utility to prevent rogue application, add-ins and malware from running within Registry, Windows Explorer, StartUp and especially IE Browser – some of which compromises your machine and chews on System resources. This were widely used in the good old WinXP days (and still may be?).
HJ-Split
I’ve had this software for many ages. It allows you to split any file into smaller manageable chunks. Although there’s popular software as Win-Rar and WinZip which are licensed, this little tool is free!
Malwarebytes
Simply, one of the best anti-malware on the market and one of my favorites. I had lucked out and had a non-expiry license when the product was fairly still new. Nonetheless the price of $24.95 is worth every penny for the year subscription on 2 devices. I’ve snagged a copy of their Technician Tools while it’s still free from their other products offered.