Msgbox on return of empty cell in range

warlord

New Member
Joined
May 27, 2009
Messages
35
Hi all,

I need to add some code to a recorded macro to throw up a messagebox if a cell within the copy range is blank. Basicly when people use the sheet all the cells in the row need to be filled. below is the code i have.

Code:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/07/2010 by Mark
'
'
    Range("C6:I10").Select
    Range("I10").Activate
    Selection.Copy
    Sheets("Holidays List").Visible = True
    Sheets("Holidays List").Select
    Range("A3").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Sheets("Holidays List").Visible = False
End Sub
i did try a piece of code suggested elsewhere but the range would not work as anything entered into C6 would let the macro run even if all the other cells in the range are blank.

Thanks for any help
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
If WorksheetFunction.CountA(Range("C6:I10")) <> Range("C6:I10").Count Then
    MsgBox "You must complete C6:I10", vbExclamation
    Exit Sub
End If
 

warlord

New Member
Joined
May 27, 2009
Messages
35
Try

Code:
If WorksheetFunction.CountA(Range("C6:I10")) <> Range("C6:I10").Count Then
    MsgBox "You must complete C6:I10", vbExclamation
    Exit Sub
End If


Excellent works perfectly thank you very much
 

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,219
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top