How to remove duplicate rows using VBA code?

I have excel sheet with data up to 5k rows. What I need to do is to remove duplicate rows having similar value at Column A and C. The problem now I need to combine data at Column F before I can delete duplicate rows. By using formula I don’t think it will work. Therefore I have to write Macro to perform this work. The code that work as below. Hope this code will help who ever have same problem.

<pre style="overflow: auto; width: 94%;">

Option Explicit

Sub RemoveDuplicateAndBringUpCommon()

    Dim x As Long, y As Long

    Dim ActWs As Worksheet

    Set ActWs = ActiveSheet

    x = 2

    y = x + 1

    Do While ActWs.Range("C" & x).Value <> ""

        Do While ActWs.Range("C" & y).Value <> ""

            If ActWs.Range("A" & x).Value = ActWs.Range("A" & y).Value And _

            ActWs.Range("C" & x).Value = ActWs.Range("C" & y) Then

                ActWs.Range("F" & x) = ActWs.Range("F" & x) & "," & ActWs.Range("F" & y)

                ActWs.Rows(y).EntireRow.Delete

            Else

                y = y + 1

            End If

        Loop

        x = x + 1

        y = x + 1

    Loop

End Sub

<pre>

Please help to share or comment if got better ideas.

Thank You.

How to record or run macro from Microsoft Excel 2007?

In Excel 2007 you can record and playback macro from “View” tab, at the right end click at macro you’re able to see the optional either view macro or record macro. Another method is through Developer tab but this tab by default is hidden. To show “Developer” tab you must go to Office button (top left corner) and click, Another window open and click at Excel Options, under “Popular” label you must tick at “Show developer tab in the ribbon” and click OK. Under this “Developer” tab you are able to see Visual Basic Editor, Available Macros, Record Macro, security setting and etc.

Thanks

How to save your recorded macro in Microsoft Excel 2007?

In previous version of Microsoft Excel with or without macro the file extension will be the same which is (Filename.xls) but when we use Microsoft Excel 2007 and onward the file extension will be different. Without Macro (Filename.xlsx) and with macro (Filename.xlsm), Therefore when you save file with macro you must select save as type “Excel Macro-Enable Workbook” instead of “Excel Workbook” otherwise your macro will be gone.

Thanks

How to check available Rows in Microsoft Excel Using VBA?

Hi,

Some time people is still using Microsoft Excel 2003 and certain may up to 2010. Major different between these version is number of rows available. So to set stop point when we loop using VBA code we need to know number of rows available. Below code will trigger this point.

Cells.Rows.Count

Please try it and comment. Thanks

How to Open Visual Code for the recorded Macro?

As I mentioned earlier Macro actually using visual basic programming language to perform given task. In order for you create more complicated macro it will be more better for you to understand the basic programming language which is VB. This learning process will be more easier after you record the macro immediately check the VB code. Record ---then Check the VB code. After several time now you will understand What code to be use for the specific task given. There are many ways to open our visual basic editor:
1st Method:

1) Click visual basic symbol at the toolbar
2) Another window open
3) Click at Module1
4) Immediately you can see the VB code.






















2nd Method

1) Go to Tools>Macro>Macros..
2) Pop up menu ask you to choose macro
3) Choose your macro
4) If you desired to run the macro just click run
5) But this time you need to check VB code click edit
6) Now immediately you can see the code representing your task

I just wanted you guys to familiarize on how to open the VB code..That All
Practice, practice...& practice. O.k

Make sure you are fully understand how to check VB code immediately after record certain macro.












This is VB code editor look like.

How to edit recorded macro? Pls wait for me..Tks

How to run macro in Microsoft Excel from different workbook?

The steps are the same, the only thing you must open your workbook with macro inside and minimize. Now open your new workbook and follow the previous step:

• Go to >Tools>Macro>Macros
• Choose your Macro
  

All macro will be listed down if you select Macros in: All open Workbooks

• Click at your desired macro and click run
• End.

Re-cap :
Now you are willing to :

1. record macro.
2. Run macro.
3. Run macro from different workbook.

Our Next Chapter will be more interesting because involved code editing:

How to edit recorded macro? Pls wait for me..Tks

How ro run macro in Microsoft Excel?

Before you can run your recorded macro you have to change your security level to medium:

• Go to > Tools > security
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
• Tick medium Security level
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
• Click O.K
• End.

Now your security level is medium and you can run the macro immediately.

I assume that your previous workbook still open which is inside your sheet 1, range A1:C10 consist of <Geniusideas> word.

Now go to sheet 2 and follow these steps:

• Go to > Tools > macro > Macros….
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
• Choose your Macro (If you have many type of macro recorded previously).
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
• Click “Run”
• End.

Now you have same result as sheet 1 but this time in sheet 2.
Pls try and try again with different step until you are familiar with it.

If you close and save your excel file, every time you open the file the macro must be enable before you can start using.

Our Next Chapter:

How to run macro in Microsoft Excel from different workbook? Pls wait for me..Tks