Friday 14 August 2015

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.

Monday 27 July 2015

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