blank cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Curt - 27 May 2008 17:53 GMT

Have the following to check for blank cells. Would like to have message box
appear if blank cells exist. Then give user option to correct. OR Maybe best
to just return to worksheet being checked if blank cells exist?
Thanks

Worksheets("MailD").Activate
Dim Blanks As Long
Dim LastRow As Integer
Blanks = msgbox(Prompt:="Do you have blanks to complete?,If not sure
Click on Yes", Buttons:=vbYesNo)
If Blanks = vbYes Then
LastRow = Cells(Rows.Count, "A").End(xlUp).row
range("A2:F2").Resize(LastRow - 4).Select
Userform1_Main_form.Hide
Selection.specialcells(xlCellTypeBlanks).Select
End If

Reply to this Message

Bernie Deitrick - 27 May 2008 18:59 GMT

You could trap the error that occurs when you look for blankcells:

Sub TryNow()
Dim Blanks As VbMsgBoxResult
Dim LastRow As Long
Dim myR As Range

With Worksheets("MailD")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
On Error GoTo NoBlanks
Set myR = .Range("A2:F2").Resize(LastRow - 4).SpecialCells(xlCellTypeBlanks)
Blanks = MsgBox("Do you want to complete blanks?", vbYesNo)
If Blanks = vbYes Then
myR.Select
End If
NoBlanks:
End With

End Sub

Signature

HTH,
Bernie
MS Excel MVP

> Have the following to check for blank cells. Would like to have message box
> appear if blank cells exist. Then give user option to correct. OR Maybe best
[quoted text clipped - 12 lines]
> Selection.specialcells(xlCellTypeBlanks).Select
> End If

Reply to this Message