June 11th, 2016 Administrator
Excel – Get Unique List – Method 1
|
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
|
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
Excel |
No Comments »
June 11th, 2016 Administrator
An interesting post I found via MS forum. Zip files with 7-ZIP – Method 1
|
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: zip command line
Posted in
VBScript |
No Comments »
June 11th, 2016 Administrator
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 |
Tags: blank line, remove last line
Posted in
VBScript |
No Comments »
June 11th, 2016 Administrator
|
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 |
Tags: cmd display, cmd results, read cmd query
Posted in
VBScript |
No Comments »
June 11th, 2016 Administrator
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.
Tags: rdp, remote access, team viewer
Posted in
Applications |
No Comments »
June 11th, 2016 Administrator
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”.
Tags: 8bit, chip music, chip tune, keygen music
Posted in
Applications |
No Comments »
June 11th, 2016 Administrator
tsearch_16b 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 […]
Tags: cheat, cheatengine, memory editor
Posted in
Applications |
No Comments »
June 10th, 2016 Administrator
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?).
Tags: anti-virus, malware
Posted in
Applications |
No Comments »
June 10th, 2016 Administrator
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!
Tags: large uploads, split files
Posted in
Applications |
No Comments »
June 10th, 2016 Administrator
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 […]
Tags: anti malware, anti-virus, antivirus, security
Posted in
Applications |
No Comments »