nezaam.com » 2016

Excel – Get Unique List

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

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

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

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

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 Excel | No Comments »

Zip with VBS

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

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 & """"

Zip files with 7-ZIP – Method 2

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

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 & """"

End If

End Sub

Tags: zip command line

Posted in VBScript | No Comments »

Remove Last Line From Text

June 11th, 2016 Administrator

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

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)

objFile.Close

Tags: blank line, remove last line

Posted in VBScript | No Comments »

Output Command Prompt Results

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

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

Tags: cmd display, cmd results, read cmd query

Posted in VBScript | No Comments »

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 »

MilkyTracker

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 »

TSearch

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 »

HiJackThis

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 »

HJ-Split

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 »

Malwarebytes

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 »

Excel – Get Unique List

Zip with VBS

Remove Last Line From Text

Output Command Prompt Results

TeamViewer

MilkyTracker

TSearch

HiJackThis

HJ-Split

Malwarebytes

Categories

Recent Posts

Archives

Meta