Finding blank cell

[Guest]

Hai iam creating standard template where cells having different formulas &
validation. My query is i dont want to left any cells blank. Is there any
macro to find the blank cell (within certain range) and if it find blank
cell, error message has to come stating particular cell number is blank.

 

[Guest]

Hi,

Right click the sheet tab, view code and paste this in.

Sub blanks()
Set myrange = Range("A1:A100") 'Change to suit
For Each c In myrange
If found <> 1 And IsEmpty(c.Value) Then
found = 1
myAddress = c.Address
ElseIf found = 1 And IsEmpty(c.Value) Then
Count = Count + 1
End If
Next
MsgBox myAddress & " has not been populated and there are " _
& Count & " other blanks"
End Sub

Mike

 

[Guest]

Thanks mike
itz working fine . but if all the cells are filled i want to show separate
message that all cells are filled.

 

[Guest]

Then use this moddified version:-

Sub blanks()
Set myrange = Range("A1:A10") 'Change to suit
For Each c In myrange
If found <> 1 And IsEmpty(c.Value) Then
found = 1
myAddress = c.Address
ElseIf found = 1 And IsEmpty(c.Value) Then
Count = Count + 1
End If
Next
If Count > 0 Then
MsgBox myAddress & " has not been populated and there are " _
& Count & " other blanks"
Else
MsgBox "All cells filled"
End If
End Sub

Mike

 

[Mike Fogleman]

That will fail if only 1 cell is left blank. Need to use found as the
criteria.

Sub blanks()
Dim myrange As Range, c As Range
Dim found As Integer
Dim myaddress As String
Dim count As Long
Set myrange = Range("A1:A10") 'Change to suit
For Each c In myrange
If found <> 1 And IsEmpty(c.Value) Then
found = 1
myaddress = c.Address
ElseIf found = 1 And IsEmpty(c.Value) Then
count = count + 1
End If
Next
If found = 1 Then
MsgBox myaddress & " has not been populated and there are " _
& count & " other blanks"
Else
MsgBox "All cells are filled"
End If
End Sub

Mike F

 

[Guest]

Thanks itz working great. In addition to this ............
I had assign this macro to a command button in excel sheet. By pressing the
button the macro is working fine. I want to automatically disappear/hide that
command button once after the message box shows 0 blank cells

 

[Mike Fogleman]

Put these lines wherever you want to show/hide the button. Change the sheet
reference to your sheet with the button.

Sheet1.CommandButton1.Visible = True
Sheet1.CommandButton1.Visible = False

Mike F