Remove Duplicates Rows completely from sheet

Hey How are You friends ? I know you are fine but I am in a big Problem and I know you will help me after solving my problem. Today I got a task  for removing duplicate rows from sheet, and my guide provided me two scenarios. First scenarios is not so hard and it is easily done by me but second scenarios is not we could not say it is so hard although I have solve this Problem also but  the way from which I have solve this problem is not satisfactory for me hence please give me some sun shine to come out from this darkness. I am discussing both scenarios here and please provide suitable answer for doing this task.
Note : Value of all column approximately remains same but
First Scenario : Contains Parameter that decide Duplicates

In this case first column of sheet contains duplicate values as shown in figure below so, it easy to remove all those rows by using RemoveDuplicates function 

In this figure Date is parameter by which we decide row is repeated or not, if it is repeated then we have to delete that row as I am doing by using this code.

Sub firstScanarios()

Dim last_Row As Integer
Sheets("sheet1").Activate
                          
'first count number of rows

last_Row = Range("A" & Rows.Count).End(xlUp).Row

'RemoveDuplicates fnction checks duplicate values inside sheet and remove automatically from sheet

Range("A2" & ":" & "D" & last_Row).RemoveDuplicates Columns:=Array(1), Header:=xlNo

MsgBox "Done"

End Sub
After Adding this code inside Developer mode

I use command Alt+F8 and obtain this result


In this case I don’t obtain any problem and my target is completed successfully.
Second Scenario :
In this case sheet is like this 


And I have to remove duplicates rows from sheet for this purpose I have perform same action as I previously done but because my duplicate deciding parameter column 2 so I delete all those parameters which after second column but can’t remove those columns which before second column so, to remove these columns I used another logic that I checked all those rows which are blank after performing first logic  I got such result :

I used Same code as I had done previously only I have changed deciding parameter column

Sub secondScanarios()

Dim last_Row As Integer

Sheets("sheet2").Activate

'first count number of rows

last_Row = Range("A" & Rows.Count).End(xlUp).Row

'RemoveDuplicates fnction checks duplicate values inside sheet and remove automatically from sheet

Range("B2" & ":" & "D" & last_Row).RemoveDuplicates Columns:=Array(1), Header:=xlNo
MsgBox "Done"End Sub

To remove all those rows completely which contains duplicates I used another logic

For i = 1 To last_Row

‘Column 2  contains deciding parameter so checks after completing first logic which cell contains null value

If Cells(i, 2).Value = "" Then        

Rows(i & ":" & Rows.Count).Delete

End If

Next i
Hence our complete code is here for second scenario

Sub secondScanarios()

Dim last_Row As Integer

Sheets("sheet2").Activate

'first count number of rows

last_Row = Range("A" & Rows.Count).End(xlUp).Row

'RemoveDuplicates fnction checks duplicate values inside sheet and remove automatically from sheet

Range("B2" & ":" & "D" & last_Row).RemoveDuplicates Columns:=Array(1), Header:=xlNo

'Second logic for delete duplicate rows completely

For i = 1 To last_Row

If Cells(i, 2).Value = "" Then

Rows(i & ":" & Rows.Count).Delete

End If

Next i

MsgBox "Done"

End Sub
And hence my final code is

Sub secondScanarios()

Dim last_Row As Integer

Sheets("sheet2").Activate

'first count number of rows

last_Row = Range("A" & Rows.Count).End(xlUp).Row

'RemoveDuplicates fnction checks duplicate values inside sheet and remove automatically from sheet

Range("B2" & ":" & "D" & last_Row).RemoveDuplicates Columns:=Array(1), Header:=xlNo

'Second logic for delete duplicate rows completely

For i = 1 To last_Row

If Cells(i, 2).Value = "" Then

Rows(i & ":" & Rows.Count).Delete

End If

Next i

MsgBox "Done"


End Sub

Note : To solve second scenario I used two steps to solve this problem so if you have better option then please write inside comment box for its proper solution.

Here is link of sheet which contains scenarios click here 

Comments

Post a Comment

Popular posts from this blog

Export data from mysql db to csv file using java

Image
Hi friends ! Today I am discussing about simple but very important scenario. When we create any application then during creation of application we have face many scenarios like 1.Taking information from user and storing these information inside database : Generally such type of Operation is performed by user using form application 2.Taking Data from Database and Displaying on Page : We can perform such type of Operation in various ways like using JSP,JSTL tags or you can perform such operations using JQUERY also. 3.Taking Data from Database and download it in Excel or CSV format : In this we want our data from database and store it inside Excel or text files for further use. And we have chosen third scenario from discussion for performing this Operation we have to need three files exportCsv.html  :- this contains a button for clicking and performing exporting data from database Operation ExportData.java  :- It is a servlet class which defines connectivity fro...
Keep reading

Secure Database Connectivity in node.js with mysql

Image
Hi Everyone! Yesterday my friend Andrea from Italy asked me a question about secure Database connectivity in node.js with mysql i.e. he wants to a secure database connection in node.js for some secure keys like username,password,database name that we use during database connectivity.His problem is obviously right from security point of view as we know that JavaScript is client side scripting knowledge so it is compiled on client side so after inspecting page some one can be able to view these secure keys which I discussed previously. I surf internet and found there are various ways to solve this problem I am not discussing all of them here but also I discussing here two of them and after this discussion I think Andrea and other reader who are facing with same problem can be able to resolve their problem. I choose two ways to solve this problem. Connection code in app.js file Create a Json file Connection code in app.js file : Javascript is client side language and code i...
Keep reading

Java Web Scraping

Image
Today I introduced you from a new word i.e. Web scraping this word is used for extracting data from a website for some purpose. In this technique we extract large amount data from website and store inside our local file or we can also store these data inside our database for personal use .Now this technique is widely used all around the world because in the age of Information data is every thing.As much amount of you have as much power you have.Most of programming languages contains package for Web Scraping such in python we used BeautifulSoup.  BeautifulSoup is a fantastic tool of python for web Scraping. I don't know much about python and I am good in java so, here I am discussing here  how we can scarp data from website using concepts of Java? Now, we are presenting a little package for Java web Scarping. Jsoup is an open source Java HTML parser. Jsoup is an open source HTML parser which is used for scraping in Java.It uses DOM,CSS and Jquery-like methods for ...
Keep reading