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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Try

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

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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
Back
Top