Maaf ada revisi dikit, '============================= Sub CekKosong() Dim intCol As Integer,
Dim lstRow As Integer, rgCheck As Range
Const stRow As Integer = 2
Const maxRow As Integer = 10000
intCol = 1 'misal kolom A / kolom pertama yang dicek
Set rgCheck = Range(Cells(stRow, intCol), Cells(maxRow, intCol).End(xlUp))
If Evaluate("=SUMPRODUCT(ISBLANK(" & rgCheck.Address & ")*1)") > 0 Then
MsgBox "Ada yang kosong"
rgCheck.SpecialCells(xlCellTypeBlanks).Select
Else
'tidak ada yang kosong
End If
End Sub
'==========================
From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com]
On Behalf Of zainul_ulum[at]yahoo[dot]com
Sent: Wednesday, March 20, 2013 9:59 AM
To: belajar-excel@yahoogroups.com
Subject: RE: [belajar-excel] Pencarian cel dengan nilai kosong
Mas Tio
Bisa dicoba cara lain menggunakan sumproduct:
'=============================
Sub CekKosong()
Dim intCol As Integer, rgKolom As Integer
Dim lstRow As Integer, rgCheck As Range
Const stRow As Integer = 2
Const maxRow As Integer = 10000
intCol = 1 'misal kolom A / kolom pertama yang dicek
Set rgCheck = Range(Cells(stRow, 1), Cells(maxRow, 1).End(xlUp))
If Evaluate("=SUMPRODUCT(ISBLANK(" & rgCheck.Address & ")*1)") > 0 Then
MsgBox "Ada yang kosong"
rgCheck.SpecialCells(xlCellTypeBlanks).Select
Else
'tidak ada yang kosong
End If
End Sub
'==========================
Pada 20 Maret 2013 08.56, <tio.ad...@ptssb.co.id
<mailto:tio.adjie%40ptssb.co.id> > menulis:
Dear Be-Exceller,
Saya coba mencari sel yang kosong dengan meng klik Go To special -blank dan
langsung di isi dengan 0, pertanyaannya adakah cara code VBA , bila cel yang
kosong tidak process pencarian, tapi kalau ada maka process pencarian
dilakukan.
Ini code VBA yang sudah saya buat :
Dim NumberBrsStdHrsOrder As Integer
Dim rngKosong As Range
Cells.Find("Order").Activate
NumberBrsStdHrsOrder = Range(Selection, Selection.End(xlDown)).Rows.Count +
4
Cells(1, 1).Value = 0
Cells.Find("STD HOURS").Activate
Selection.AutoFilter
Cells(1, 1).Select
Selection.Copy
Range(Cells(6, 2), Cells(NumberBrsStdHrsOrder, 2)).Select
Set rngKosong = Range(Cells(6, 2), Cells(NumberBrsStdHrsOrder,
2)).SpecialCells(xlCellTypeBlanks)
If rngKosong Is Nothing Then 'Kok ini gak berhasil kenapa yah ??
MsgBox ("Tidak ada")
Else
Selection.SpecialCells(xlCellTypeBlanks).Select
ActiveSheet.Paste
End If
Terima kasih,
Wassalam,
Tio
<<image001.jpg>>
<<image002.jpg>>
