Skip to main content

Macro code to check duplicate in excel column,Blank all Worksheets

Macro code to check duplicate in excel column

This is simple Macro code example to check duplicate in excel column. If you have very long list data in single column and have lot of duplicate or redundant data then its headache to find duplicate value.

By using following code tricks you can find it easily in excel.



 Function LastRow(ByVal Filename As String) As Long  
 Dim ix As Long  
 ix = Worksheets(Filename).UsedRange.Row - 1 + Worksheets(Filename).UsedRange.Rows.Count  
 ' ix = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count  
 LastRow = ix  
 End Function  
 Function LastCol(ByVal Filename As String) As String  
 Dim ix As Integer  
 ix = Worksheets(Filename).UsedRange.Column - 1 + Worksheets(Filename).UsedRange.Columns.Count  
 LastCol = ConvertToLetter(ix)  
 End Function  
 Function ConvertToLetter(iCol As Integer) As String  
 ' Dim iAlpha As Integer  
 ' Dim iRemainder As Integer  
 ' iAlpha = Int(iCol / 27)  
 ' iRemainder = iCol - (iAlpha * 26)  
 ' If iAlpha > 0 Then  
 ' ConvertToLetter = Chr(iAlpha + 64)  
 ' End If  
 ' If iRemainder > 0 Then  
 ' ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)  
 ' End If  
 Dim columnName As String  
 Dim modulo As Integer  
 While iCol > 0  
 modulo = (iCol - 1) Mod 26  
 columnName = Chr(65 + modulo) + columnName  
 iCol = Int((iCol - modulo) / 26)  
 Wend  
 ConvertToLetter = columnName  
 End Function  
 Function ColumnRange(ByVal Filename As String, ByVal Rangehead As String) As String  
 Dim r As Range  
 Dim ic As Integer  
 Dim rt As String  
 Dim CN As String  
 Set r = Worksheets(Filename).Range("A1:" & LastCol(Filename) & "1")  
 For i = 1 To r.Count  
 If Rangehead = r(1, i) Then  
 ic = i  
 i = r.Count  
 End If  
 Next  
 If ic = 0 Then  
 rt = ""  
 Else  
 CN = ConvertToLetter(ic)  
 rt = CN & "2:" & CN & LastRow(Filename)  
 End If  
 ColumnRange = rt  
 End Function  
 Sub configs(ByVal Target As Range)  
 Dim Flag As Boolean  
 Dim strcmp As String  
 Flag = False  
 Dim NOCalc As Boolean  
 NOCalc = False  
 For i = 1 To LastRow("exppk")  
 If Target.Address = ("$A$" & i) Then  
 Dim rng As Range  
 Set rng = Worksheets("exppk").Range("$A$" & i)  
 strcmp = rng(1, 1)  
 If strcmp = "Error" Then  
 NOCalc = True  
 End If  
 Flag = True  
 i = LastRow("exppk") + 1  
 End If  
 Next i  
 If NOCalc Then  
 Else  
 If Flag Then  
 i = LastRow("exppk") + 1  
 For j = 1 To LastRow("exppk")  
 Dim Rg As Range  
 Set Rg = Worksheets("exppk").Range("$A$" & j)  
 If Target.Address <> ("$A$" & j) Then  
 If strcmp = Rg(1, 1) Then  
 rng(1, 1).Value = "Error"  
 j = LastRow("exppk") + 1  
 End If  
 End If  
 Next j  
 End If  
 End If  
 End Sub  

Code to blank all sheets in excel

If you have lot of things written in sheet and want to blank or clear that worksheet then you can try this micro code.

 Sub BlankAll()  
 Dim BlankSh As Worksheet  
 Application.DisplayAlerts = False  
 On Error Resume Next  
 ActiveWorkbook.Worksheets("Blank").Delete  
 On Error GoTo 0  
 Application.DisplayAlerts = True  
 Set BlankSh = ActiveWorkbook.Worksheets.Add  
 BlankSh.Name = "Blank"  
 For Each sh In ActiveWorkbook.Worksheets  
 If InStr(sh.Name, "Blank") Then  
 Else  
 Application.DisplayAlerts = False  
 On Error Resume Next  
 ActiveWorkbook.Worksheets(sh.Name).Delete  
 On Error GoTo 0  
 Application.DisplayAlerts = True  
 End If  
 Next  
 End Sub  

Popular posts from this blog

Resolved : Power BI Report connection error during execution

Getting Below Power BI Report connection error during execution . Error: Something went wrong Unable to connect to the data source undefined. Please try again later or contact support. If you contact support, please provide these details. Underlying error code: -2147467259 Table: Business Sector. Underlying error message: AnalysisServices: A connection cannot be made. Ensure that the server is running. DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259 Microsoft.Data.Mashup.ValueError.DataSourceKind: AnalysisServices Microsoft.Data.Mashup.ValueError.DataSourcePath: 10.10.10.60;T_CustomerMaster_ST Microsoft.Data.Mashup.ValueError.Reason: DataSource.Error Cluster URI: WABI-WEST-EUROPE-redirect.analysis.windows.net Activity ID: c72c4f12-8c27-475f-b576-a539dd81826a Request ID: dfb54166-c78f-4b40-779f-e8922a6687ad Time: 2019-09-26 10:03:29Z Solution: We found report connection not able to connect to SQL Analysis service so tried below option. Re

Song- Khamoshiyan Piano keyboard Chord,Notation and songs Lyrics

All songs notation and chords at one place

Song : O Saathi Re Film : Mukhathar Ka Sikkandhar Uses : C D D# E G A Note : The numbers at the end of the lines indicate line numbers. Pallavi: O saathi re, tere binaa bhi kya jina, tere binaa bhi kya jina A- C D D#....,D D C DD E...C..CA-...,D D C DD E...CC.......1 Play line 1 again phulon men khaliyon men sapnom ki galiyon men GGG...GAGE.. GGG G A G E.................................................2 tere bina kuchh kahin naa E A G E D C D D#.......................................................................3 tere binaa bhi kya jina, tere binaa bhi kya jina D D C DD E....C..CA-..., D D C DDE....CC.............................4 Charanam: har dhadkan men, pyaas hai teri, sanson men teri khushboo hai CCC C D C A-, CCC C D C A-, DDD DED CD EE.. CCCC......................5 is dharthi se, us ambar tak, meri nazar men tu hi tu hai CCC C D C A-, CCC C D C A-, DDD DED CD EE.. CCCC......................6 pyaar yeh tute naa GGG... GAG D#......E.........................