Makasih Pak atas pencerahannya.sangat mebantu
From: [email protected] [mailto:[email protected]] On Behalf Of jkssxls Sudarsono Sent: 14 Maret 2012 9:57 To: Belajar-Excel Yahoo Groups Subject: RE: [belajar-excel] Tanya VBA delete row dg kriteria tertentu Misal di worksheet , dimana ada data yang akan dihapus. mulai Sel A2 : JENIS NAMA HARGA 1 MINUMAN JUS JERUK 10,000 1 2 MINUMAN JUS JAMBU 12,000 2 3 MAKANAN SOTO AYAM 10,000 3 4 MINUMAN ES TEH 5,000 4 5 BUAH STRAWBERRY 25,000 5 6 MAKANAN BAKSO 10,000 6 72,000 Kriteria di worksheet Filternya , mulai sel B2 : JENIS NAMA HARGA *MINUMAN* >=6000 Maka yang akan di hapus ( memenuhi kriteria ) : JENIS NAMA HARGA 1 MINUMAN JUS JERUK 10,000 1 2 MINUMAN JUS JAMBU 12,000 2 22,000 hasil Data setelah penghapusan : JENIS NAMA HARGA 1 MAKANAN SOTO AYAM 10,000 1 2 MINUMAN ES TEH 5,000 2 3 BUAH STRAWBERRY 25,000 3 4 MAKANAN BAKSO 10,000 4 5 50,000 5 6 6 Adapun macronya : Sub Mc_AdvFilterDelete_1() ' UNTUK MENCEGAH WORKSHEET YG TIDAK SEHARUSNYA Dim worksheetnya As Worksheet Set worksheetnya = ActiveSheet If UCase(worksheetnya.Name) <> "HAPUS" Then Exit Sub Dim daerahfilter As Range Sheets("Filternya").Activate Range("B2").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Set daerahfilter = Selection Dim daerahData As Range worksheetnya.Activate Range("B2").Select If ActiveSheet.AutoFilterMode Then Selection.AutoFilter End If Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Set daerahData = Selection Selection.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ daerahfilter, Unique:=False Dim jumbaris, jumkolom As Long jumbaris = daerahData.Rows.Count - 1 jumkolom = daerahData.Columns.Count Set daerahData = daerahData.Offset(1, 0).Resize(jumbaris, jumkolom) Dim sel As Range Dim bAlerts As Boolean bAlerts = Application.DisplayAlerts Application.DisplayAlerts = False Dim strRow As String For Each sel In daerahData.Rows If Not (sel.Height = 0 Or sel.Hidden) Then strRow = strRow & "," & sel.Address End If Next If ActiveSheet.AutoFilterMode Then Selection.AutoFilter End If ' Tampilkan semua Data ActiveSheet.ShowAllData If strRow <> "" Then Range(Mid(strRow, 2)).Select Selection.Delete Shift:=xlUp End If Application.DisplayAlerts = bAlerts End Sub Kalau ingin hapus yang mengandung kol, tomat , dst , maka isi Filternya : Nama *kol* *tomat* *mangga* *rambutan* *kobis* *bayam* *jambu* _____ To: [email protected] From: [email protected] Date: Tue, 13 Mar 2012 18:46:19 +0700 Subject: [belajar-excel] Tanya VBA delete row dg kriteria tertentu Dear Pakar Bagaimana cara delete row yg mengandung kata tertentu Missal di sheet 1 kolom B saya pingin menhapus row yg mengandung kata buah kol tomat mangga rambutan kobis bayam jambu Sebelum dan sesudahnya terima kasih Salam Ocha --- Mods --- Contoh workbook berisi data secukupnya akan lebih membantu. Terutama, daftar nama buah yang menjadi kriteria. ------------ _____ No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1424 / Virus Database: 2113/4869 - Release Date: 03/13/12
