intan

Komunitas Blogger Universitas Sriwijaya

 

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. :)



PDF | DOC | DOCX

Komentar:

03 Desember 2010 pukul 15:31 WIB
Joe mengatakan...
macro saya jalankan tetapi gak mau jalan (disable) dan ada tulisan because the security level is set to high.. bla..bla... bla... kenapa ya? bantuin saya dong.... please

11 Juni 2010 pukul 10:17 WIB
phietung mengatakan...
makasih buat tutorialnya...berguna sekali dan bermanfaat... semoga sukses selalu..

02 Juni 2010 pukul 16:14 WIB
widya mengatakan...
makasiih ,, smile grin

04 Februari 2010 pukul 10:29 WIB
PRAS mengatakan...
Penjelasan MENGHAPUS BARIS YG TDK DIPERLUKAN, detail & ok buanget nich,,,,,,, Ngomong2 ane baru belajar nyang kaya gitu,,? (macro excel) kalo ngga keberatan boleh dong dikasih tahu rumus (skrip) dasar ama penjelasan nyang kaya diatas " i = 2 'Pembacaan data dimulai baris ke 2",,, moga2 pahalanya terus ngalir dari orang2 yg make rumus juragan,,,,,

22 Desember 2009 pukul 11:21 WIB
Ridwan mengatakan...
Saya punya kasus yang hampir sama seperti di atas, hanya saja berkaitan dengan Chart. Anggap saja begini : - Chart saya mempunyai source data di A1:B13 - A1 berisi judul "Bulan" - A2-A13 berisi angka 1 s.d 12 (jika sekarang bulan agustus, maka angka 9, 10, 11, dan 12 benilai 0, semua angka diperoleh dari transfer data dari query di Acces) - B1 berisi judul "Jumlah penjualan" - B2:B13 berisi angka penjualan Bagaimana caranya agar baris "Bulan" yang bernilai=0 dapat dihapus. saya sdh coba code di atas, namun tidak ada hasilnya. kira-kira apa kesalahan saya. Mohon bantuannya, karena chart ini harus di update pada saat diperlukan tanpa menghapus manual (menhindari lupa menghapus) Thanks.

Kirim Komentar Anda:

Nama : Nama Anda (wajib diisi)
E-Mail : E-Mail (tidak dipublikasikan)
Situs : Website, Blog, Facebook, dll
Komentar :
(wajib diisi)
Verifikasi :
<-- isi kode di atas (wajib diisi)

grinLOLcheesesmilewinksmirkrolleyesconfused
surprisedbig surprisetongue laughtongue rolleyetongue winkraspberryblank starelong face
ohhgrrrgulpoh ohdownerred facesickshut eye
hmmmmadangryzipperkissshockcool smilecool smirk
cool grincool hmmcool madcool cheesevampiresnakeexcaimquestion