Sum Unique/Distinct Values in Excel

Sum Unique/Distinct Values in Excel:-

Public Function DISTINCTSUM(Rg As range)

Dim rCell As range

Dim cCells As New Collection

Dim vValue As Variant



' create a unique no duplicate value collection

For Each rCell In Rg

On Error Resume Next

cCells.Add rCell.Value, CStr(rCell.Value)

Next rCell


' sum all the data in previous collection

For Each vValue In cCells

DISTINCTSUM = vValue + DISTINCTSUM

Next vValue



Set cCells = Nothing

End Function

Remove Non-AlphaNumeric Characters from String

Remove Non-AlphaNumeric Characters from String:-


Function GETALPHANUMERIC(text)

str_all = "abcdefghijklmnopqrstuvwxyz1234567890"

For lenstr = 1 To Len(text)

If InStr(str_all, LCase(Mid(text, lenstr, 1))) Then

GETALPHANUMERIC = GETALPHANUMERIC & Mid(text, lenstr, 1)

End If

Next

End Function

How to Get a Full File Path in Excel?

Function FULLFILENAME()
FULLFILENAME = Application.ActiveWorkbook.FullName
End Function

Empty Clipboard Excel Using Windows Empty Clipboard function

Empty Clipboard Excel Using Windows Empty Clipboard function:-

Public Declare Function OpenClipboard Lib "user32" (ByVal NewOwner As Long) As Boolean

Public Declare Function EmptyClipboard Lib "user32" () As Boolean

Public Declare Function CloseClipboard Lib "user32" () As Boolean


Sub ClearClipboard()

' call ClearClipboard() from your other VBA macro to clear/empty Windows Clipboard

If OpenClipboard(0) Then

EmptyClipboard

CloseClipboard

End If

End Sub

Empty Clipboard Excel Using Excel Application properties

Empty Clipboard Excel Using Excel Application properties:-

Application.CopyObjectsWithCells = False

Get Position of Last Column Containing Data with Excel VBA:-

Function LastColumn() As Long

Dim ix As Long

ix = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count

LastColumn = ix

End Function