Checking for blank rows in database - with apologies to Norman Jon

Good morning all!
The reason for the apology to Norman is I started a different thread
yesterday where he was helping me, but I can't find it at work this morning,
so I hope he doesn't think I'm being rude!

I'm working with a database that occupies columns B:L and I'm trying to
prevent users from having a row where Bx:Lx contains all blank cells, as I
use currentregion to add and delete rows, and it doesn;t work properly if it
encounters a blank row.

This is what I tried in my Worksheet_Change macro (which works in all other
respects). I'm attempting a multiple IF statement to check each cell in the
current row from column B to column L. If all cells are empty, a value is
entered into column F of the current row.

The problem is that although no error messages are displayed re: syntax, the
code just doesn't work when I empty all the cells between column B and column
L in the current row. I've tried this in lots of flavours, including IF
len("B" & .row) = 0, but nothing seems to work. I guess it's something do do
with the way in which I'm trying to concatenate the column letter and the
..row of the Target, but I'm now completely stumped.

Can any of you good people out there on a Monday morning give me a hand?

Thanks in advance (and to you too, Norman for helping a sad old man with
nothing better to do on a Sunday afternoon!)

Pete

With Target

'---------------------------------------------------------------------------------
If .Column >= 2 Then
If .Column <= 12 Then
If Application.isblank("B" & .Row) Then
If Application.isblank("C" & .Row) Then
If Application.isblank("D" & .Row) Then
If Application.isblank("E" & .Row) Then
If Application.isblank("F" & .Row) Then
If Application.isblank("G" & .Row) Then
If Application.isblank("H" & .Row) Then
If Application.isblank("I" & .Row) Then
If Application.isblank("J" & .Row) Then
If Application.isblank("K" & .Row) Then
If Application.isblank("L" & .Row) 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

Hi Peter,

Define a name ("MyDatabase") for the database area, and replace the previous
code with:

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

Set rng = Range("MyDatabase") '<<===== DEFINE
Set rng2 = Intersect(rng, Target.EntireRow)


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
'<<'======================


---
Regards,
Norman



"Peter Rooney" <[email protected]> wrote in message
news:[email protected]...
> Good morning all!
> The reason for the apology to Norman is I started a different thread
> yesterday where he was helping me, but I can't find it at work this
> morning,
> so I hope he doesn't think I'm being rude!
>
> I'm working with a database that occupies columns B:L and I'm trying to
> prevent users from having a row where Bx:Lx contains all blank cells, as I
> use currentregion to add and delete rows, and it doesn;t work properly if
> it
> encounters a blank row.
>
> This is what I tried in my Worksheet_Change macro (which works in all
> other
> respects). I'm attempting a multiple IF statement to check each cell in
> the
> current row from column B to column L. If all cells are empty, a value is
> entered into column F of the current row.
>
> The problem is that although no error messages are displayed re: syntax,
> the
> code just doesn't work when I empty all the cells between column B and
> column
> L in the current row. I've tried this in lots of flavours, including IF
> len("B" & .row) = 0, but nothing seems to work. I guess it's something do
> do
> with the way in which I'm trying to concatenate the column letter and the
> .row of the Target, but I'm now completely stumped.
>
> Can any of you good people out there on a Monday morning give me a hand?
>
> Thanks in advance (and to you too, Norman for helping a sad old man with
> nothing better to do on a Sunday afternoon!)
>
> Pete
>
> With Target
>
> '---------------------------------------------------------------------------------
> If .Column >= 2 Then
> If .Column <= 12 Then
> If Application.isblank("B" & .Row) Then
> If Application.isblank("C" & .Row) Then
> If Application.isblank("D" & .Row) Then
> If Application.isblank("E" & .Row) Then
> If Application.isblank("F" & .Row) Then
> If Application.isblank("G" & .Row) Then
> If Application.isblank("H" & .Row) Then
> If Application.isblank("I" & .Row) Then
> If Application.isblank("J" & .Row) Then
> If Application.isblank("K" & .Row) Then
> If Application.isblank("L" & .Row) 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
>
>

Thanks, Norman.

I'll give it another try - get back to you later.

Pete



"Norman Jones" wrote:

> Hi Peter,
>
> Define a name ("MyDatabase") for the database area, and replace the previous
> code with:
>
> '======================>>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim rng As Range
> Dim rng2 As Range
> Dim rw As Range
>
> Set rng = Range("MyDatabase") '<<===== DEFINE
> Set rng2 = Intersect(rng, Target.EntireRow)
>
>
> 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
> '<<'======================
>
>
> ---
> Regards,
> Norman
>
>
>
> "Peter Rooney" <[email protected]> wrote in message
> news:[email protected]...
> > Good morning all!
> > The reason for the apology to Norman is I started a different thread
> > yesterday where he was helping me, but I can't find it at work this
> > morning,
> > so I hope he doesn't think I'm being rude!
> >
> > I'm working with a database that occupies columns B:L and I'm trying to
> > prevent users from having a row where Bx:Lx contains all blank cells, as I
> > use currentregion to add and delete rows, and it doesn;t work properly if
> > it
> > encounters a blank row.
> >
> > This is what I tried in my Worksheet_Change macro (which works in all
> > other
> > respects). I'm attempting a multiple IF statement to check each cell in
> > the
> > current row from column B to column L. If all cells are empty, a value is
> > entered into column F of the current row.
> >
> > The problem is that although no error messages are displayed re: syntax,
> > the
> > code just doesn't work when I empty all the cells between column B and
> > column
> > L in the current row. I've tried this in lots of flavours, including IF
> > len("B" & .row) = 0, but nothing seems to work. I guess it's something do
> > do
> > with the way in which I'm trying to concatenate the column letter and the
> > .row of the Target, but I'm now completely stumped.
> >
> > Can any of you good people out there on a Monday morning give me a hand?
> >
> > Thanks in advance (and to you too, Norman for helping a sad old man with
> > nothing better to do on a Sunday afternoon!)
> >
> > Pete
> >
> > With Target
> >
> > '---------------------------------------------------------------------------------
> > If .Column >= 2 Then
> > If .Column <= 12 Then
> > If Application.isblank("B" & .Row) Then
> > If Application.isblank("C" & .Row) Then
> > If Application.isblank("D" & .Row) Then
> > If Application.isblank("E" & .Row) Then
> > If Application.isblank("F" & .Row) Then
> > If Application.isblank("G" & .Row) Then
> > If Application.isblank("H" & .Row) Then
> > If Application.isblank("I" & .Row) Then
> > If Application.isblank("J" & .Row) Then
> > If Application.isblank("K" & .Row) Then
> > If Application.isblank("L" & .Row) 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
> >
> >
>
>
>

Norman,

I got there in the end, with your help and a morning's hard labour!

The way I was going about it was inherently flawed, as I ran my
"DefineDatabase" routine, to calculate the first and last row and column,
within my Worksheet_Change macro. The problem was, as a row became blank,
running"DefineDatabase" redefined range names as being up to, but not
including the blank row.

This meant that not only was the offending blank row not flagged (and
deleted, which was the whole point of the exercise), but the resulting ranges
only included rows up to where the blank row had been, not the whole database.

So, I simply made sure I ran "DefineDatabase" AFTER the blank Checking
routines, incorporated your mods and it's all OK now.

Thanks for replying, both yesterday and today

Regards

Pete




"Norman Jones" wrote:

> Hi Peter,
>
> Define a name ("MyDatabase") for the database area, and replace the previous
> code with:
>
> '======================>>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim rng As Range
> Dim rng2 As Range
> Dim rw As Range
>
> Set rng = Range("MyDatabase") '<<===== DEFINE
> Set rng2 = Intersect(rng, Target.EntireRow)
>
>
> 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
> '<<'======================
>
>
> ---
> Regards,
> Norman
>
>
>
> "Peter Rooney" <[email protected]> wrote in message
> news:[email protected]...
> > Good morning all!
> > The reason for the apology to Norman is I started a different thread
> > yesterday where he was helping me, but I can't find it at work this
> > morning,
> > so I hope he doesn't think I'm being rude!
> >
> > I'm working with a database that occupies columns B:L and I'm trying to
> > prevent users from having a row where Bx:Lx contains all blank cells, as I
> > use currentregion to add and delete rows, and it doesn;t work properly if
> > it
> > encounters a blank row.
> >
> > This is what I tried in my Worksheet_Change macro (which works in all
> > other
> > respects). I'm attempting a multiple IF statement to check each cell in
> > the
> > current row from column B to column L. If all cells are empty, a value is
> > entered into column F of the current row.
> >
> > The problem is that although no error messages are displayed re: syntax,
> > the
> > code just doesn't work when I empty all the cells between column B and
> > column
> > L in the current row. I've tried this in lots of flavours, including IF
> > len("B" & .row) = 0, but nothing seems to work. I guess it's something do
> > do
> > with the way in which I'm trying to concatenate the column letter and the
> > .row of the Target, but I'm now completely stumped.
> >
> > Can any of you good people out there on a Monday morning give me a hand?
> >
> > Thanks in advance (and to you too, Norman for helping a sad old man with
> > nothing better to do on a Sunday afternoon!)
> >
> > Pete
> >
> > With Target
> >
> > '---------------------------------------------------------------------------------
> > If .Column >= 2 Then
> > If .Column <= 12 Then
> > If Application.isblank("B" & .Row) Then
> > If Application.isblank("C" & .Row) Then
> > If Application.isblank("D" & .Row) Then
> > If Application.isblank("E" & .Row) Then
> > If Application.isblank("F" & .Row) Then
> > If Application.isblank("G" & .Row) Then
> > If Application.isblank("H" & .Row) Then
> > If Application.isblank("I" & .Row) Then
> > If Application.isblank("J" & .Row) Then
> > If Application.isblank("K" & .Row) Then
> > If Application.isblank("L" & .Row) 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
> >
> >
>
>
>