Making sure that cells within a row in a database aren't blank

[Guest]

Good afternoon, weekend Excelers!

I currently have a database in cells B14:L19

I'm trying to prevent users from having blank rows in the database by using
the Worksheet_Change event to check that if a change is made to any cells,
that this doesn't result in all eleven cells in the row being blank.

As you can see from the code below, I've tried lots of different ways to
(for example if I'm in row 16) to check that B16:L16 aren't blank, but
without much success. I get lots of "invalid qualifier" messages.

Can anyone advise me of the best way to go about this?

Thanks in advance

Pete Rooney


With Target

'---------------------------------------------------------------------------------
If .Column >= 2 Then
If .Column <= 12 Then
MsgBox (.Column & " " & .Row)

If Application.WorksheetFunction.isblank(Cells(Target.Row, 2)) Then
' If "C" & Target.Row.Value = 0 Then
' If "D" & Target.Row.Value = 0 Then
' If "E" & Target.Row.Value = 0 Then
' If "F" & Target.Row.Value = 0 Then
' If "G" & Target.Row.Value = 0 Then
' If "H" & Target.Row.Value = 0 Then
' If "I" & Target.Row.Value = 0 Then
' If "J" & Target.Row.Value = 0 Then
' If "K" & Target.Row.Value = 0 Then
' If "L" & Target.Row.Value = 0 Then
MsgBox ("You CAN'T have blank rows in the
database!")
' Range("F" & Target.Row).Formula = "Blank
Eliminator"
' End If
' End If
' End If
' End If
' End If
' End If
' End If
' End If
' End If
' End If
End If
End If
End If

'---------------------------------------------------------------------------------
If .Column = 11 Then
DefineDatabase
If .Row >= FirstDBRow Then
If .Row <= FinalDBRow Then

'---------------------------------------------------------------------------
If .Formula = "Core Infrastructure" Then

 

[Norman Jones]

Hi Pete,

Perhaps, something like this will do what you want:

'===========================>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim rng2 As Range
Dim rw As Range

Set Rng = Range("A2:L20") '<<===== CHANGE
Set rng2 = Intersect(Rng, Target)


If Not rng2 Is Nothing Then
For Each rw In rng2.Rows
If Application.CountA(rw.Cells) = 0 Then
MsgBox ("Database row " & rw.Row _
& " is empty. You CAN'T have " _
& "blank rows in the database!")
End If
Next
End If
End Sub
'<<===========================

Change the range address to accord with the database range.

 

[Guest]

Norman,

Thanks for this.

A couple of issues - I need the range to be dynamic as the database will
extend or reduce, depending on user action. However, I have a routine that
determines the first and last cells of the range, so it's not too much of a
problem.

However, when I tried your code, I get the message even though there's
something in one of the columns.
I never have any luck with intersect!

Any thoughts?

Pete

 

[Norman Jones]

Hi Pete,

A couple of issues - I need the range to be dynamic as the database will
extend or reduce, depending on user action. However, I have a routine that
determines the first and last cells of the range, so it's not too much of
a
problem.

Fine. As you intimate, instead of defining a static range, establish and
define the boundaries.

Alternatively, perhaps you can use a dynamic range (nammed, say
"MyDatabase") and set the rng variable to MyDatabase.

If you are not familiar with dynamic ranges, see debra Dalgleish at:

http://www.contextures.com/xlNames01.html#Dynamic

However, when I tried your code, I get the message even though there's
something in one of the columns.
I never have any luck with intersect!

My fault! Try changing:

to

Set rng2 = Intersect(Rng, Target.EntireRow)