Loncat ke daftar isi utama

Bagaimana cara menghapus seluruh kolom berdasarkan nilai header di Excel?

Dalam banyak kasus, Anda mungkin perlu menghapus seluruh kolom berdasarkan nilai header di Excel. Misalnya, Anda perlu menghapus semua kolom yang berisi nilai "lama" di header. Dalam tutorial ini, kami akan menunjukkan kepada Anda metode untuk menghapus seluruh kolom berdasarkan nilai header secara detail.

Hapus seluruh kolom berdasarkan nilai header dengan kode VBA
Hapus seluruh kolom berdasarkan nilai header dengan Kutools for Excel

Hapus seluruh kolom berdasarkan nilai header dengan kode VBA

Anda dapat menghapus seluruh kolom berdasarkan nilai header dengan kode VBA. Silakan lakukan sebagai berikut.

1. tekan lain + F11 tombol secara bersamaan untuk membuka Aplikasi Microsoft Visual Basic jendela.

2. Dalam Aplikasi Microsoft Visual Basic window, klik Menyisipkan > Modul. Dan kemudian salin dan tempel kode di bawah ini ke jendela Modul.

Kode VBA: hapus seluruh kolom berdasarkan nilai header

Sub DeleteSpecifcColumn()
	Set MR = Range("A1:D1")
	For Each cell In MR
		If cell.Value = "old" Then cell.EntireColumn.Delete
	Next
End Sub

Catatan:

1) dalam kode, “tua"Adalah nilai tajuk yang ingin Anda hapus berdasarkan seluruh kolom. Harap ubah ke nilai tajuk Anda secara manual. Dan A1: D1 adalah rentang kolom yang ingin Anda gunakan.
2) Kode ini peka huruf besar/kecil.

3. tekan F5 kunci untuk menjalankan kode, seluruh kolom dengan header sama dengan teks yang ditentukan akan segera dihapus.

Hapus seluruh kolom berdasarkan nilai header dengan Kutools for Excel

Dengan Kutools untuk Excel'S Pilih Sel Spesifik utilitas, Anda dapat dengan mudah memilih semua kolom yang berisi nilai tajuk tertentu terlebih dahulu, lalu menghapus kolom yang dipilih ini sekaligus. Silakan lakukan sebagai berikut.

Sebelum melamar Kutools untuk Excel, Mohon unduh dan instal terlebih dahulu.

1. Pilih rentang yang berisi kolom yang perlu Anda hapus, lalu klik Kutools > Pilih > Pilih Sel Spesifik. Lihat tangkapan layar:

2. Dalam Pilih Sel Spesifik kotak dialog, pilih Seluruh kolom pilihan dalam Tipe pilihan bagian, dan di Tipe khusus daftar drop-down, pilih sama opsi, lalu ketik nilai header di kotak berikut ini. Dan terakhir klik OK tombol. Lihat tangkapan layar:

3. Klik OK tombol di kotak dialog popping up lain, kemudian kolom yang berisi nilai header tertentu dipilih segera.

4. Klik kanan tajuk kolom dari kolom yang dipilih, lalu klik Delete dari menu klik kanan. Kemudian semua kolom yang dipilih dihapus sekaligus.

  Jika Anda ingin memiliki uji coba gratis (30 hari) dari utilitas ini, silahkan klik untuk mendownloadnya, lalu lanjutkan untuk menerapkan operasi sesuai langkah di atas.

Alat Produktivitas Kantor Terbaik

🤖 Kutools AI Ajudan: Merevolusi analisis data berdasarkan: Eksekusi Cerdas   |  Hasilkan Kode  |  Buat Rumus Khusus  |  Analisis Data dan Hasilkan Grafik  |  Aktifkan Fungsi Kutools...
Fitur Populer: Temukan, Sorot, atau Identifikasi Duplikat   |  Hapus Baris Kosong   |  Gabungkan Kolom atau Sel tanpa Kehilangan Data   |   Putaran tanpa Formula ...
Pencarian Super: VLookup Beberapa Kriteria  |   VLookup Nilai Berganda  |   VLookup di Beberapa Lembar   |   Pencarian Fuzzy ....
Daftar Drop-down Lanjutan: Buat Daftar Drop Down dengan Cepat   |  Daftar Drop Down yang Bergantung   |  Multi-pilih Drop Down List ....
Manajer Kolom: Tambahkan Jumlah Kolom Tertentu  |  Pindahkan Kolom  |  Alihkan Status Visibilitas Kolom Tersembunyi  |  Bandingkan Rentang & Kolom ...
Fitur Unggulan: Fokus Kisi   |  Tampilan Desain   |   Bar Formula Besar   |  Manajer Buku Kerja & Lembar   |  Perpustakaan Sumberdaya (Teks otomatis)   |  Pemetik tanggal   |  Gabungkan Lembar Kerja   |  Enkripsi/Dekripsi Sel   |  Kirim Email berdasarkan Daftar   |  Filter Super   |   Filter Khusus (filter tebal/miring/coret...) ...
15 Perangkat Teratas:  12 Teks Tools (Tambahkan Teks, Hapus Karakter, ...)   |   50 + Grafik jenis (Gantt Chart, ...)   |   40+ Praktis Rumus (Hitung usia berdasarkan ulang tahun, ...)   |   19 Insersi Tools (Masukkan Kode QR, Sisipkan Gambar dari Jalur, ...)   |   12 Konversi Tools (Angka ke Kata, Konversi Mata Uang, ...)   |   7 Gabungkan & Pisahkan Tools (Lanjutan Gabungkan Baris, Pisahkan Sel, ...)   |   ... dan banyak lagi

Tingkatkan Keterampilan Excel Anda dengan Kutools for Excel, dan Rasakan Efisiensi yang Belum Pernah Ada Sebelumnya. Kutools for Excel Menawarkan Lebih dari 300 Fitur Lanjutan untuk Meningkatkan Produktivitas dan Menghemat Waktu.  Klik Di Sini untuk Mendapatkan Fitur yang Paling Anda Butuhkan...

Deskripsi Produk

Tab Office Membawa antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Jauh Lebih Mudah

  • Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
  • Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
  • Meningkatkan produktivitas Anda sebesar 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari!
Comments (13)
No ratings yet. Be the first to rate!
Liz
Liz
This comment was minimized by the moderator on the site
Hi, I need to delete a load of columns containing # in the headings - my understanding of VBA is very limited and the report has about 60 columns. The # is not always in the same location in each heading, so I need to identify any columns with the # in the heading and remove those columns. Is anyone able to guide me on this please?

I also need to split the report into 3 sections - the data runs across the worksheet (ie column A - H is Sales invoices; column I-Z is Purchase invoices and column AA-AG is Payments - each of these need to go onto a seperate worksheet - it is very labour intensive doing this manually so I am hoping someone can help me in VBA with this one as well.

Thanks
Reply
0
0
Resnick
Resnick
This comment was minimized by the moderator on the site
Hola, tengo una hoja de excel con varios datos, digamos 4 columnas y 12 filas, me gustaría poder imprimir cada fila en un folio separado en una plantilla, es decir, tenemos una hoja con varios nombres y apellidos y teléfono, quiero imprimir tantas páginas como filas tenga la hoja de excel, he inte tado varias cosas pero no encuentro la forma. Gracias de antemano
Reply
0
0
crystal
crystal     Resnick
This comment was minimized by the moderator on the site
Hi,
If you want to print each row in a separate page in the worksheet, here is a suggestion that you insert page breaks every 1 row and then print them.
Methods provided in this article may do you a favor. Please have a try. Thank you.
How To Insert Page Break Every X Rows In Excel?[/url]
https://www.extendoffice.com/documents/excel/1774-excel-insert-page-break-every-row.html
Reply
Report
0
0
Jonathan
Jonathan
This comment was minimized by the moderator on the site
The VBA example states that the code will delete all columns whose headers CONTAIN "old." That is not the case. The = operator will only delete exact matches. You need to use the LIKE operator with a wildcard.
Fixed code:Sub DeleteSpecifcColumn()
Set MR = Range("A1:D1")
For Each cell In MR
If cell.Value LIKE "old*" Then cell.EntireColumn.Delete
Next
End Sub

Reply
0
0
crystal
crystal     Jonathan
This comment was minimized by the moderator on the site
Hi,Thank you for your correction and sharing. 
Reply
Report
0
0
Mark
Mark
This comment was minimized by the moderator on the site
Hi, is it possible to do this with multiple column names?
Reply
0
0
Rahul
Rahul     Mark
This comment was minimized by the moderator on the site
What if the headers are starting from 4th row
Reply
0
0
crystal
crystal     Mark
This comment was minimized by the moderator on the site
Hi Mark,
Try the below code. In the eighth line, please enclose each column name with double quotes and separate them by comma. Hope I can help. Thank you.

Sub DeleteSpecifcColumn()

Dim xFNum, xFFNum, xCount As Integer

Dim xStr As String

Dim xArrName As Variant

Dim MR, xRg As Range

On Error Resume Next

Set MR = Range("A1:N1")

xArrName = Array("old", "new", "get") 'enclose each column name with double quotes and separate them by comma

xCount = MR.Count

xStr = xArrName(xFNum)

For xFFNum = xCount To 1 Step -1

Set xRg = Cells(1, xFFNum)

For xFNum = 0 To UBound(xArrName)

xStr = xArrName(xFNum)

If xRg.Value = xStr Then xRg.EntireColumn.Delete

Next xFNum

Next

End Sub
Reply
Report
0
0
Paul Rod
Paul Rod     crystal
This comment was minimized by the moderator on the site
I think line 12 needs to be changed to get the code to work. Original line 12 "Set xRg = Cells(1, xFFNum)" modified line 12 "Set xRg = MR(1, xFFNum)"
Sub DeleteSpecifcColumn()
Dim xFNum, xFFNum, xCount As Integer
Dim xStr As String
Dim xArrName As Variant
Dim MR, xRg As Range
On Error Resume Next
Set MR = Range("A1:N1")
xArrName = Array("old", "new", "get") 'enclose each column name with double quotes and separate them by comma
xCount = MR.Count
xStr = xArrName(xFNum)
For xFFNum = xCount To 1 Step -1
Set xRg = MR(1, xFFNum)
For xFNum = 0 To UBound(xArrName)
xStr = xArrName(xFNum)
If xRg.Value = xStr Then xRg.EntireColumn.Delete
Next xFNum
Next
End Sub
Reply
0
0
Elias
Elias
This comment was minimized by the moderator on the site
Hey! This works well until you have two contiguous columns with the same header. Say your range is A1:A5, and A2 and A3 have the header 'old'. It will delete A2, but then the A3 will move to the place A2 used to occupy, and the code will skip it, as it will be looking at the new A3, the next cell on its range.
Reply
0
0
crystal
crystal     Elias
This comment was minimized by the moderator on the site
Hi Elias,
As you mentioned, A2 and A3 have he same header "old". But they are in the same column, after applying the code, the whole column A will be removed immediately.
I am not sure I get your point. Would be nice if you could provide screenshot of what you are trying to do.
Reply
Report
0
0
Leonard Staggs
Leonard Staggs
This comment was minimized by the moderator on the site
Sorry if this seems super basic but this is my first time trying vba. Using this how would I apply it to delete multiple column headers. I am actually trying to only keep certain header columns and delete the rest of a changing list.
Reply
0
0
Ata
Ata     Leonard Staggs
This comment was minimized by the moderator on the site
In the first code just copy the 4th row and change the "old" into the second column name and it will work
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations