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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,507
Messages
5,837,751
Members
430,515
Latest member
K_Lynch

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