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.

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



"gane" wrote:

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.

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

"Mike H" wrote:

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



"gane" wrote:

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.

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" wrote:

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
"Mike H" wrote in message
...
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

"gane" wrote:

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

"Mike H" wrote:

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



"gane" wrote:

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.

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
"gane" wrote in message
...
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" wrote:

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
"Mike H" wrote in message
...
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

"gane" wrote:

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

"Mike H" wrote:

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



"gane" wrote:

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.