MS Excel – Macro, Menghapus Baris Yang Tidak Diperlukan

excel 2003Suatu hari seorang rekan minta tolong dibikinin macro di Excel. Dia punya permasalahan, ada sebuah file spreadsheet (Excel) berisi output ETABS – saya juga kurang jelas waktu itu output apa (sepertinya sih reaksi tumpuan), yang jelas terdiri dari 6 kolom: FX, FY, FZ, MX, MY, dan MZ. Kami rasa para pembaca sudah “pamiliar” dengan simbol-simbol di atas. Nah, dia tuh pengen “membuang” baris-baris yang FX, FY, dan FZ-nya bernilai 0 alias zero. Kalau misalnya datanya cuma ada 50 baris sih nggak masalah, hapus manual saja. Tapi data yang dia punya ada 4000an data! Butet dah.

Akhirnya dia “menantang”-ku. “3 menit!..” katanya. Tenang saja,… juragan optimis nih. Flowchart-nya sudah langsung ter-desain dengan rapi di kepala.

Akhirnya dengan sekali-dua kali trial-error, akhirnya jadilah kode berikut:

Sub sopbuntut()
i = 2 'Pembacaan data dimulai baris ke 2
Do
x = ActiveSheet.Cells(i, 1).Value
If x = "0" Then
ActiveSheet.Rows(i).Delete
Else
i = i + 1
End If
Loop Until ActiveSheet.Cells(i, 1).Value = ""
End Sub

Bagaimana cara kerjanya, cara bikinnya, dan cara pakainya? Ini dia penjelasannya.
Misalnya kita punya data seperti gambar di bawah ini. Untuk contoh kasus, juragan cuma tampilkan beberapa baris data saja.

excel_case_before

Kita ingin melenyapkan baris-baris yang FX, FY, dan FZ-nya bernilai nol, karena mungkin itu agak mengganggu.

  1. Pastikan MS Excel anda aktif. Klik menu Tools -> Macro -> Macros… Atau, gunakan shortkey Alt+F8, atau Alt+T+M+M. Kotak dialog macro akan terbuka.
    excel macro
  2. Isi nama makro sesuai selera anda di kolom macro name. Misalnya sopbuntut. Ingat, ada beberapa peraturan-peraturan yang berlaku dalam penamaan makro, misalnya nggak boleh pake spasi, nggak pake simbol-simbol aneh seperti #,$,@, dkk, nggak boleh pake cabe dan nggak pake seledri. (!?)
    excel_macro_dialog
  3. Klik tombol “create”, anda akan dibawah ke dunia bawah sadar, dan tepuk tangan penonton akan mengiringi anda lebih jauh lagi ke dalam alam bawah sadar anda. Anda akan berada di dunia coding, di sinilah anda akan menuliskan kode-kode rahasia yang mampu memberikan sugesti positif buat anda.
    excel_VB_empty
  4. Pastikan kursor anda berada di antara baris Sub sopbuntut(), dan baris End sub. Artinya, di situlah anda boleh menuliskan kode sesuka hati anda.
  5. Sesuaikan (baca: salin) kode di atas dengan kode anda. Juragan akan coba bercerita tentang maksut dan tujuannya.
    excel_VB_code
  6. Baris pertama, i = 2. Ini adalah inisialisasi. Sesuai dari tabel di atas, data mulai dibaca pada baris kedua. Kenapa bukan baris pertama? Karena baris pertama itu baris judul! Kalau misalnya datanya dimulai pada baris ketiga, berarti i=3. Nah, tanda petik tunggal di Visual Basic menandakan sebuah komentar. Apapun yang ada setelah tanda petik ‘ (dalam satu baris tentunya), tidak akan dieksekusi oleh VB. Oiya, VB itu mengeksekusi perintah baris per baris. Jadi, sebuah pernyataan sebisa mungkin harus ditulis dalam satu baris. Jika panjang, sebaiknya disingkat saja.
  7. Baris kedua, Do. Do adalah bentuk perulangan. Kali ini kita menggunakan format Do... Loop Until ... VB akan mengeksekusi semua perintah di dalam Do... Loop, sampai akhirnya tercapai kondisi akhir yaitu isi sel yang ditunjuk sama dengan kosong alias null, atau ditandai dengan “”.
  8. Baris ketiga, x = ActiveSheet.Cells(i, 1).Value. ActiveSheet adalah sheet yang aktif. Macro harus dijalankan pada sheet aktif yang tepat, yaitu sheet di mana tabel data berada, misalnya Sheet1, Sheet2, dll. Cells(i,1) merujuk kepada sel baris ke-i, kolom ke 1 (yaitu kolom FX). Pada saat inisialisasi, nilai i sama dengan 2, jadi Cells(i,1) merujuk kepada sel A2 (baris-2, kolom-1). Sementara parameter Value menunjukkan kita akan mengambil nilai atau isi dari sel A2 tersebut. Nilai itu dimasukkan ke variabel x.
  9. Baris keempat dan kelima, pengecekan nilai x. Jika nilainya sama dengan 0, maka baris tersebut segera dihapus. Perintahnya cukup jelas, ActiveSheet.Rows(i).Delete. Setelah baris dihapus, apa yang terjadi? Ini urutannya :
    • baris yang aktif terhapus,
    • semua baris yang ada di bawah akan naik. Misalnya jika baris 3 dihapus, maka baris 4 akannaik ke 3, baris 5 naik ke 4, dst.
    • VB akan keluar dari kondisi if x=0, dan mengecek kondisi Loop, apakah ActiveSheet.Cells(i,1) isinya kosong atau tidak. Kalau kosong, Loop dihentikan. Kalau tidak, ulang lagi dari baris ketiga.
  10. Baris keenam dan ketujuh, kondisi lain jika nilai x bukan nol, maka lanjut ke baris berikutnya, yaitu i+1.
  11. Begitu seterusnya sehingga tercapai kondisi di mana ActiveSheet.Cells(i,1) tidak ada isinya alias = “”.
  12. That’s it. Cuma itu, simpel tapi sederhana… (!?!?)

Bagaimana cara menjalankannya? Kembali lagi ke langkah #1 di atas. Tekan tombol Alt + F8. Di situ macro sopbuntut telah nongkrong dengan gagahnya. Tinggal nunggu dipilih, dan klik tombol Run.
excel_macro_run

Hasil akhirnya bagaimana? Kurang lebih seperti gambar di bawah.

excel_case_after

nb : sebenarnya ada cara lain menyelesaikan masalah tersebut tanpa menggunakan macro. Excel sendiri menyediakan tools tersebut. Yaitu melalui menu Data -> Filter -> Auto Filter. Kemudian pilih kondisinya, yaitu cells doesn’t equal “0”. Kemudian select hasilnya, copy dan paste di sheet lain. 🙂

7 thoughts on “MS Excel – Macro, Menghapus Baris Yang Tidak Diperlukan

  1. budi santoso

    Salam Kenal Mas…

    Mas saya mau tanya, saya dapat software download juga di internet. file dalam bentuk excell mas, tp kok gak bisa jalan ya mas.. softwarenya kayaknya sih macro/Spreadsheet gitu…

    bisa bantuin gak mas, supaya saya bisa input datanya trus bisa di running…

    makasih ya mas…

    Reply
  2. yani

    salam kenal,
    klo mau menghapus data, dengan kriteria “date/bulan” tapi tabelnya ga standard seperti biasa lazimnya (mksdnya ada baris2 yg kosong/space baris) gimana ya.. help ya.. soalnya tiap bulan aq hrs ngapusin data2 bulan sebelumnya sampai menjadi kosong lagi, lama bgt kallee… thx b’4

    Reply
  3. pebriyanto

    trimakasih ilmunya……. saya orang baru gbung, alias baru belajar……… sekalian minta sarannya biar lebih maju…………….. pokoknya trimakasih

    Reply
  4. admin

    @Kholid, Trima kasih.
    Biar bisa menghitung metoda cross dengan cepat, ya banyak-banyak latihan. 🙂 Jujur saja, metode cross itu hampir tidak tersentuh di dunia praktisi. Makanya kapabilitas kami menggunakan metode itu sudah menurun, harus buka-buka literatur lagi. Tapi, untuk bentuk turunannya misalnya metode Moment Distribution, itu masih bisa dipake untuk analisis balok menerus.
    Kunci metode Cross dan Moment Distribusi adalah Jepit-Lepas-Cross, yang diulang-ulang terus sampai puas.. 🙂

    Reply
  5. Kholid

    Bung Juragan
    Thanks Buat ilmunya … semoga sukses slalu …
    dan gimana caranya supaya cepat menghitung dengan metode cross yang rumit itu….?

    Reply
  6. irwan

    Bung Juragan..
    thanks bagt atas ilmu2nya… smoga bs bermanfaat buat kitasemua..
    dan bisa ditambah lg program2 free lainnya.. 🙂
    GBU.. pizzz..

    Reply

Boleh komentar di sini