Pada Excel versi 2007 atau yang lebih baru, area worksheet dibatasi oleh jumlah kolom maksimal 16.364 dan jumlah baris maksimal 1.048.576. Namun pada kenyataanya, user lebih sering menggunakan sebagian kecil saja dari jumlah sel yang tersedia dalam worksheet. Penggunaan sebagian area dalam worksheet tanpa pembatasan scroll area terkadang cukup menyulitkan dalam melakukan observasi lembar kerja excel. Karena itulah maka disarankan untuk membatasi scroll area.
Keuntungan pembatasan scroll area:
Pembatasan scroll area dapat dilakukan dengan beberapa cara, antara lain:
Tehnik paling sederhana untuk membuat batasan lembar kerja excel adalah dengan cara menyembunyikan baris dan kolom tidak terpakai.
Langkahnya cukup mudah:
Hasilnya dapat dilihat seperti screenshot di bawah ini.
Dari gambar di atas, kita dapat melihat, excel hanya menampilkan kolom dan baris untuk area yang digunakan saja, yaitu baris 1 s/d 15 dan kolom A s/d H. Hal tersebut karena area yang tidak digunakan sudah disembunyikan (di-hide).
Metode kedua untuk menetapkan batasan scroll area adalah dengan cara menentukan valid range spesifik. Valid range dapat di-setting melalui property worksheet.
Caranya:
Keterangan: tanda absolute ($) akan muncul otomatis meskipun kita mengetik valid range tanpa tanda dolar $.
Setelah selesai, kembali ke worksheet dan kita bisa buktikan bahwa scroll range akan dibatasi sesuai valid range yang kita ketikan di jendela properties. Jika scroll area yang kita gunakan sedikit saja atau kurang dari ukuran range yang terlihat (visible range), maka kolom dan baris diluar scroll area masih akan nampak. Namun kita tidak bisa menyeleksi sel / range dan mengetik pada sel diluar scroll area. Silahkan dibuktikan sendiri.
Sayangnya, kita tidak bisa menyimpan setting scrollarea yang dibuat dengan cara edit properties karena scroll area akan di-reset kembali setelah close file.
Untuk mengatasi hal tersebut, kita bisa menggunakan code vba untuk menetapkan scroll area. Code ini ditempatkan pada even worksheet_activate sehingga akan dijalankan setiap kali worksheet yang disisipkan code ini diaktifkan.
Caranya:
Contoh penempatan code dapat dilihat pada screenshot di bawah ini.
Gambar di atas menunjukan contoh penempatan code untuk membatasi scroll area sheet 3 pada range A1:J50. Code berjalan setiap kali sheet 3 diaktifkan (berpindah dari sheet lain ke sheet 3).
Untuk membuat batasan scroll area pada sheet lainnya, silahkan masuk kembali ke VBA editor, kemudian pada jendela project explorer, double klik nama sheet yang akan disisipkan code. Selanjutnya ketik code yang sama dengan contoh sebelumnya pada modul object sheet yang diinginkan.
Kita juga bisa berexperimen dengan merubah range scroll area. Silahkan range A1:J50 diedit kembali dengan range yang anda inginkan.
Kita bisa menghapus secara manual dengan cara menghapus scroll area pada property worsksheet. Cara yang lebih cepat dan disarankan adalah dengan menggunakan macro / VBA. Adapun codenya sangat sederhana.
Misalnya:
Untuk menempatkan code pada module VBA, ikuti langkah berikut:
Screenshot di bawah ini menggambarkan langkah-langkah penempatan code pada modul standar.
Setelah langkah di atas diikuti satu-persatu, maka kita bisa menghapus scroll range menggunakan makro.
Caranya:
Cara Ketiga dan yang paling fleksibel adalah dengan menetapkan used range sebagai scroll area. Untuk menempatkan code, caranya serupa dengan cara kedua yaitu:
Karena code diatas akan dijalankan ketika sheet diaktifkan maka kita harus pindah dulu ke sheet lain, kemudian masuk kembali (mengaktifkan) ke sheet dimana code sudah disisipkan. Maka kita akan mendapati sheet dengan batasan scroll area sudah terpasang.
Jika kita ingin mengedit areal di luar used range, maka kita bisa mereset sementara scroll area dengan cara yang sudah dijelaskan sebelumnya
Sampai disini, mudahan-mudahan catatan excel mengenai cara membatasi scroll area dapat difahami dan semoga bermanfaat.
Thks
Artikel terkait:
Referensi:
David Raina & Hawley 2007, Excel Hack, Tips & Tools for Streamlining Your Spreadsheet, 2nd edition. O’Reilly Media, Inc
Keuntungan pembatasan scroll area:
- Pembatasan scroll area memudahkan dalam melakukan observasi dan navigasi workhsheet.
- Jika anda pembuat tempate laporan, maka anda bisa menyembunyikan data yang tidak ingin diketahui oleh user di luar lokasi scroll area.
Pembatasan scroll area dapat dilakukan dengan beberapa cara, antara lain:
- Menyembunyikan kolom dan baris tidak terpakai
- Menentukan valid range
- Mengaktifkan range terpakai (used range)
Menyembunyikan kolom dan baris tidak terpakai
Tehnik paling sederhana untuk membuat batasan lembar kerja excel adalah dengan cara menyembunyikan baris dan kolom tidak terpakai.
Langkahnya cukup mudah:
- Seleksi semua kolom yang tidak digunakan, kemudian klik kanan ➨ hide
- Dengan cara serupa, seleksi semua baris yang tidak digunakan, kemudian klik kanan ➨ hide
Hasilnya dapat dilihat seperti screenshot di bawah ini.
Dari gambar di atas, kita dapat melihat, excel hanya menampilkan kolom dan baris untuk area yang digunakan saja, yaitu baris 1 s/d 15 dan kolom A s/d H. Hal tersebut karena area yang tidak digunakan sudah disembunyikan (di-hide).
Menentukan Valid Range Spesifik
Metode kedua untuk menetapkan batasan scroll area adalah dengan cara menentukan valid range spesifik. Valid range dapat di-setting melalui property worksheet.
Caranya:
- Klik kanan pada tab sheet yang akan ditentukan sroll area nya.
- Klik view Code, sehingga akan masuk ke VBA editor.
- Di dalam vba editor, cari jendela properties. Jika jendela properties belum terlihat, tekan F4 untuk memunculkannya.
- Pada jendela properties, pada field ScrollArea, ketikan valid range yang anda inginkan. Contoh pengisian seperti dalam gambar di bawah ini.
Keterangan: tanda absolute ($) akan muncul otomatis meskipun kita mengetik valid range tanpa tanda dolar $.
Setelah selesai, kembali ke worksheet dan kita bisa buktikan bahwa scroll range akan dibatasi sesuai valid range yang kita ketikan di jendela properties. Jika scroll area yang kita gunakan sedikit saja atau kurang dari ukuran range yang terlihat (visible range), maka kolom dan baris diluar scroll area masih akan nampak. Namun kita tidak bisa menyeleksi sel / range dan mengetik pada sel diluar scroll area. Silahkan dibuktikan sendiri.
Sayangnya, kita tidak bisa menyimpan setting scrollarea yang dibuat dengan cara edit properties karena scroll area akan di-reset kembali setelah close file.
Untuk mengatasi hal tersebut, kita bisa menggunakan code vba untuk menetapkan scroll area. Code ini ditempatkan pada even worksheet_activate sehingga akan dijalankan setiap kali worksheet yang disisipkan code ini diaktifkan.
Caranya:
- Klik kanan pada tab sheet yang akan dibatasi sroll area nya.
- Klik view Code, sehingga akan masuk ke VBA editor.
- Pada jendela project explorer, double clik nama sheet yang akan dibatasi scroll area nya sehingga muncul muncul object sheet tersebut.
- Ketik atau copy code berikut pada modul ojbect sheet:
Private Sub Worksheet_Activate()
Me.ScrollArea = "A1:J50"
End Sub
Contoh penempatan code dapat dilihat pada screenshot di bawah ini.
Gambar di atas menunjukan contoh penempatan code untuk membatasi scroll area sheet 3 pada range A1:J50. Code berjalan setiap kali sheet 3 diaktifkan (berpindah dari sheet lain ke sheet 3).
Untuk membuat batasan scroll area pada sheet lainnya, silahkan masuk kembali ke VBA editor, kemudian pada jendela project explorer, double klik nama sheet yang akan disisipkan code. Selanjutnya ketik code yang sama dengan contoh sebelumnya pada modul object sheet yang diinginkan.
Kita juga bisa berexperimen dengan merubah range scroll area. Silahkan range A1:J50 diedit kembali dengan range yang anda inginkan.
Bagaimana Jika kita ingin mengedit sel di luar scroll area?
Sebagaimana penjelasan di atas, setelah kita setting scroll area maka kita tidak bisa mengedit sel di luar scroll area. Untuk itu kita harus menghapus setting scroll area terlebih dahulu jika ingin memodifikasi area sheet di luar scrollarea.Kita bisa menghapus secara manual dengan cara menghapus scroll area pada property worsksheet. Cara yang lebih cepat dan disarankan adalah dengan menggunakan macro / VBA. Adapun codenya sangat sederhana.
Misalnya:
Sub deleteScrollArea()
ActiveSheet.ScrollArea = ""
End Sub
Untuk menempatkan code pada module VBA, ikuti langkah berikut:
- Masuk ke jendela VBA dengan cara tekan Alt + F11
- Jika module standar belum tersedia, Klik menu Insert ➨ Module
- Maka akan muncul modul baru (misalnya : Module1)
- Tempatkan code pada module 1. Bisa dilakukan dengan copy paste contoh code di atas, maupun dengan mengetiknya sendiri.
Screenshot di bawah ini menggambarkan langkah-langkah penempatan code pada modul standar.
Setelah langkah di atas diikuti satu-persatu, maka kita bisa menghapus scroll range menggunakan makro.
Caranya:
- Tekan Alt + F8 untuk memunculkan list Macro
- Pilih macro yang sudah kita buat sebelumnya (macro deleteScrollArea)
- Lalu klik Run
Mengaktifkan Range Terpakai (Used Range)
Cara Ketiga dan yang paling fleksibel adalah dengan menetapkan used range sebagai scroll area. Untuk menempatkan code, caranya serupa dengan cara kedua yaitu:
- Klik kanan tab sheet yang ingin di-setting scroll area nya
- Pada module object sheet terpilih, ketik atau copy code berikut:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.ScrollArea = Me.UsedRange.Address
End Sub
- Setelah itu, keluar kembali dari VBA editor.
Karena code diatas akan dijalankan ketika sheet diaktifkan maka kita harus pindah dulu ke sheet lain, kemudian masuk kembali (mengaktifkan) ke sheet dimana code sudah disisipkan. Maka kita akan mendapati sheet dengan batasan scroll area sudah terpasang.
Jika kita ingin mengedit areal di luar used range, maka kita bisa mereset sementara scroll area dengan cara yang sudah dijelaskan sebelumnya
Sampai disini, mudahan-mudahan catatan excel mengenai cara membatasi scroll area dapat difahami dan semoga bermanfaat.
Thks
Artikel terkait:
- Cara mencegah save as, print, dan insert sheet.
- Membuat Daftar Isi Otomatis
- Macro untuk find & replance text dalam comment.
Referensi:
David Raina & Hawley 2007, Excel Hack, Tips & Tools for Streamlining Your Spreadsheet, 2nd edition. O’Reilly Media, Inc
