blank cells

C

Curt

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
 
B

Bernie Deitrick

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top