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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" <PeterRooney@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0A25CDBF-3CD8-438F-937F-8FBFBA617064@xxxxxxxxxxxxxxxx
> 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
>
>


.

Relevant Pages

  • Re: Making sure that cells within a row in a database arent blank
    ... Private Sub Worksheet_Change(ByVal Target As Range) ... Dim rng2 As Range ... Change the range address to accord with the database range. ...
    (microsoft.public.excel.programming)
  • Re: Is This Possible ... ? Yes - Upload images to an Access database
    ... It looks like your databases folder is outside the root of your web ... I created a database exactly as you said, the only change I made was to ... and underneath an 'upload' button and a 'view images' button. ... Dim con As New Data.OleDb.OleDbConnection ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Is This Possible ... ? Yes - Upload images to an Access database
    ... It looks like your databases folder is outside the root of your web ... I created a database exactly as you said, the only change I made was to ... and underneath an 'upload' button and a 'view images' button. ... Dim con As New Data.OleDb.OleDbConnection ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Is This Possible ... ? Yes - Upload images to an Access database
    ... I created a database exactly as you said, the only change I made was to ... upload it into the 'databases' folder, and not a folder called 'App_Data', ... and underneath an 'upload' button and a 'view images' button. ... Dim con As New Data.OleDb.OleDbConnection ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Range with "Large"
    ... My test sheet is not set up with a scroll bar so I will not take the time to fully debug and re-write it. ... Go back to your worksheet and click one of the target cells. ... You should aim to run any lines of code code when you select a single cell within your target range. ... Dim i As Integer, x As Integer ...
    (microsoft.public.excel.programming)