Rumusan masalah
Kami memiliki rentang sel dengan data yang berisi sel kosong:
Tugasnya adalah menghapus sel kosong, hanya menyisakan sel dengan informasi.
Metode 1. Kasar dan cepat
- Memilih rentang asli
- Tekan tombolnya F5, tombol berikutnya Menyoroti (Spesial). Di jendela yang terbuka, pilih sel kosong(Kosong) dan klik OK.
Semua sel kosong dalam rentang dipilih.
- Kami memberikan perintah di menu untuk menghapus sel yang dipilih: klik kanan- Hapus sel (Hapus Sel) dengan pergeseran ke atas.
Metode 2: Rumus Array
Untuk menyederhanakan, beri nama rentang kerja kami menggunakan Manajer nama (Manajer Nama) tab rumus (Rumus) atau, di Excel 2003 dan yang lebih lama, menu Sisipkan – Nama – Tetapkan (Sisipkan — Nama — Tentukan)
Beri nama rentang B3:B10 kosong, rentang D3:D10 – Tidak adaKosong. Rentang harus benar-benar berukuran sama, dan dapat ditempatkan di mana saja relatif satu sama lain.
Sekarang pilih sel pertama dari rentang kedua (D3) dan masukkan rumus menakutkan ini ke dalamnya:
=IF(ROW() -ROW(NoEmpty)+1>NOTROWS(YesEmpty)-COUNTBLANK(YesEmpty);””;INDIRECT(ADDRESS(LOWEST((IF(Empty<>“”,ROW(Kosong);ROW() + ROWS(Ada yang Kosong))); LINE()-ROW(Tidak Ada yang Kosong)+1); COLUMN(Ada yang Kosong); 4)))
Dalam versi bahasa Inggris itu akan menjadi:
=IF(ROW()-ROW(NoEmpty)+1>ROWS(Kosong)-COUNTBLANK(Kosong),””,INDIRECT(ALAMAT(KECIL((IF(Kosong<>“”,ROW(Kosong),ROW() +ROWS(HaveEmpty))),ROW()-ROW(NoEmpty)+1),COLUMN(HaveEmpty),4)))
Selain itu, harus dimasukkan sebagai rumus array, yaitu tekan setelah menempel Enter (seperti biasa) dan Ctrl + Shift + Enter. Sekarang rumus dapat disalin menggunakan pelengkapan otomatis (seret tanda silang hitam di sudut kanan bawah sel) – dan kita akan mendapatkan rentang asli, tetapi tanpa sel kosong:
Metode 3. Fungsi khusus di VBA
Jika ada kecurigaan bahwa Anda sering harus mengulangi prosedur untuk menghapus sel kosong dari rentang, maka lebih baik menambahkan fungsi Anda sendiri untuk menghapus sel kosong ke set standar satu kali, dan menggunakannya dalam semua kasus berikutnya.
Untuk melakukannya, buka Editor Visual Basic (ALT + F11), masukkan modul kosong baru (menu Sisipkan – Modul) dan salin teks fungsi ini di sana:
Fungsi NoBlanks(DataRange As Range) Sebagai Variant() Dim N Selama Dim N2 Selama Dim Rng As Range Dim MaxCells Selama Dim Result() Sebagai Varian Dim R Selama Dim C Selama MaxCells = Application.WorksheetFunction.Max( _ Application.Caller.Cells.Count, DataRange.Cells.Count) Hasil ReDim(1 To MaxCells, 1 To 1) Untuk Setiap Rng Di DataRange.Cells Jika Rng.Value <> vbNullString Maka N = N + 1 Hasil(N, 1 ) = Rng.Value End If Next Rng For N2 = N + 1 To MaxCells Result(N2, 1) = vbNullString Next N2 If Application.Caller.Rows.Count = 1 Then NoBlanks = Application.Transpose(Result) Else NoBlanks = Hasil Fungsi Akhir Jika Akhir
Jangan lupa untuk menyimpan file dan beralih kembali dari Editor Visual Basic ke Excel. Untuk menggunakan fungsi ini dalam contoh kita:
- Pilih rentang sel kosong yang memadai, misalnya F3:F10.
- Masuk ke menu Sisipkan – Fungsi (Sisipkan — Fungsi)atau klik tombol Sisipkan fungsi (Sisipkan Fungsi) tab rumus (Rumus) di versi Excel yang lebih baru. Dalam kategori Ditetapkan pengguna (Ditetapkan pengguna) pilih fungsi kami tidak ada kosong.
- Tentukan rentang sumber dengan void (B3:B10) sebagai argumen fungsi dan tekan Ctrl + Shift + Enteruntuk memasukkan fungsi sebagai rumus array.
:
- Menghapus semua baris kosong dalam tabel sekaligus dengan makro sederhana
- Menghapus semua baris kosong di lembar kerja sekaligus menggunakan add-on PLEX
- Cepat isi semua sel kosong
- Apa itu makro, di mana memasukkan kode makro di VBA