Trik Menghapus Duplicates Record Menggunakan VBA

Hari pertama masuk kantor (di “tempat yang baru”) terasa asing juga. Ibarat seekor ikan yang habis dimutasi dari aquarium lama ke aquarium yang baru, saya pun membutuhkan waktu untuk beradaptasi (baca: menyesuaikan diri) dengan lingkungan kerja yang baru. Jadilah agenda hari ini hanya sekedar bersih-bersih meja, merakit kembali personal computer, menata rapi dokumen-dokumen kerja, membuka email (outlook) kantor, dan berinteraksi dengan rekan-rekan kerja. Belum sampai 10 menit duduk di kursi merah, hand phone tua saya berdering. Saya cek di display, ternyata Pak Boss. Ada dua hal yang beliau tanyakan hari ini, pertama mengenai ada tidaknya revisi terhadap konten report weekly VP Area yang dibuat Jum’at minggu lalu, yang kedua mengenai aktif tidaknya jaringan LAN eksisting di kantor yang baru. Lalu apa hubungannya dengan pertanyaan terakhir mas bro?, ternyata beliau meminta tolong kepada saya untuk mengisikan form cuti pribadi di account HRiS-nya. Minggu lalu beliau memang pernah rasan-rasan kepingin cuti. Ketika ditanya mengapa, beliau hanya menjawab, “Lelah, jenuh, boring. Pingin istirahat”. Dan ternyata Ambon manise menjadi destinasi cuti beliau berikutnya. Ya sudah, selamat berlibur saja kalau begitu Pak,…

Kebetulan LAN kantor masih belum normal. Di depan meja saya masih terlihat beberapa orang engineer outsource yang sedang melakukan konfigurasi router, PABX dan teman-temannya. Yo wes, daripada berdiam diri, mendingan saya otak-atik VBA saja. Kemaren saya dikomplen sama temen kantor pusat, katanya data LAC-CI Jabar banyak yang double (redundan). Gak tanggung-tanggung, ribuan jumlahnya. Karena keterbatasan waktu, akhirnya saya pakai cara yang paling sederhana saja i.e menggunakan Pivot Table. Sebenarnya ada alternatif lain, i.e menggunakan tools “Remove Duplicates”, atau menggunakan Visual Basic Application (VBA). Namun cara yang terakhir ini belum sempat saya eksplor. Untuk itu pada pembahasan kali ini saya ingin menshare sebuah trik mengenai bagaimana cara menghapus duplicated record (rows) menggunakan VBA. Mohon koreksinya kalau salah menjelaskan ya (CMIIW). Here we are,….

Worksheet di atas merupakan (salah satu) contoh data redundant. Bagaimana menghapusnya? Berikut langkah-langkahnya:

[1]. Buka MS Excel anda. Kebetulan saya pakai versi 2007.

[2]. Buka Visual Basic Editor (VBE) dengan menekan tombol “Alt” dan “F11” bersama-sama pada keyboard.

[3]. Klik menu Insert > Module. Kemudian copy-kan syntax berikut pada module.

Public Sub DeleteDuplicateRows()

‘Variable declaration

‘Variable mutlak dideklarasikan agar variable-variable tersebut bisa dibaca dan dirunning (dijalankan oleh system)

Dim R As Long

Dim N As Long

Dim V As Variant

Dim Rng As Range

‘Set error handling

‘Tujuannya adalah untuk mendeteksi error yang terjadi dan selanjutnya memberikan penanganan error sesuai dengan kemauan kita. That’s why I state “EndMacro” after “GoTo”. Artinya jika error benar-benar terjadi, maka system (seharusnya) akan menjalankan syntax EndMacro dibawah –CMIIW-.

On Error GoTo EndMacro

‘Turn off screen updating

‘Setiap program dijalankan, screen MS Excel akan terupdate (begitu kata MVP VBA Excel dalam forumnya, red). Nah untuk mempercepat akselerasi program, maka ScreenUpdating (sebaiknya) di set “False”. Tapi sebenarnya syntax dibawah ini tidaklah terlalu berpengaruh pada hasil program. Kalaupun di delete, tidak akan merubah hasil akhir..Sok lah dicoba…

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

‘Set the range variable according to whatever the user has selected.

‘Syntax dibawah ini cukup penting dalam menentukan record-record redundan mana yang akan dihapus. Sebelum system mendeteksi dan menyeleksi record-record yang double secara otomatis di database, kita harus menentukan terlebih dahulu range mana yang akan kita treatment. Kalau kita memblock sebuah kolom yang berisi record secara penuh (kolom “A:A” misalnya, red), maka syntax berikut akan secara otomatis menentukan sendiri range/array data yang system butuhkan. Intersect (pada dasarnya) merupakan irisan antara kolom (columns) dan baris (rows). Formulasinya adalah sebagai berikut: expression.Intersect(Arg1, Arg2, ...). Untuk mempermudah pemahaman, berikut ilustrasi sederhananya; Himpunan A terdiri dari {1,2,3,4,5}, himpunan B terdiri dari {2,3,4,6,7}, maka irisan A dan B adalah {2,3,4}. Itulah intersect..

Set Rng = Application.Intersect(ActiveSheet.UsedRange, _ ActiveSheet.Columns(ActiveCell.Column))

‘Begin the process of removing duplicates

‘Sebagaimana judulnya, syntax berikut bertugas memilah data-data yang redundan/double kemudian menghapusnya. Termasuk record yang teridentifikasi “NullString” atau kosong.

‘Syntax menghapus record kosong

‘System akan melooping data sejumlah “Rng.Rows.Count” record secara mundur hingga record ke satu. Jika data terbaca “VBNullString” dan lebih dari satu, maka entire row akan dihapus, demikian seterusnya hingga selesai..

N = 0

For R = Rng.Rows.Count To 1 Step -1

V = Rng.Cells(R, 1).Value

If V = vbNullString Then

If Application.WorksheetFunction.CountIf(Rng.Columns(1), vbNullString) > 1 Then

Rng.Rows(R).EntireRow.Delete

N = N + 1

End If

Else

‘Syntax menghapus record redundan

‘System akan melooping data sejumlah “Rng.Rows.Count” record secara mundur hingga record ke satu. Jika data pada row terdeteksi dan (jumlah dalam satu range-nya) lebih dari satu (redundan), maka entire row akan dihapus, demikian seterusnya hingga selesai..

If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then

Rng.Rows(R).EntireRow.Delete

N = N + 1

End If

End If

Next R

'*************'

‘ERROR HANDLER’

'*************'

‘Message Box berikut akan muncul jika error terdeteksi atau proses penghapusan data selesai... “CStr” pada CStr(N) berfungsi mengubah sebuah variant data menjadi String... “N” yang terdisplay pada MsgBox sendiri disesuaikan dengan jumlah data/record yang dihapus...

EndMacro:

Application.StatusBar = False

Application.ScreenUpdating = True

Application.Calculation = xlCalculationAutomatic

MsgBox "Duplicate Rows Deleted: " & CStr(N)

End Sub

[4]. Block kolom yang berisi data redundan dan jalankan program diatas dengan menekan tombol “run” (yang terletak dibawah menu Debug).

Nah itu saja kawan yang bisa saya bagi hari ini,..mudah-mudahan bermanfaat yo,..amieen…