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]: [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.
------------
