Checking Blank Rows

[AccessHelp]

I have a range (A1:A50) where users type in the information. In the range
users not suppose to skip a cell(s) and type in the information in the cell
after. For example, I type in the info in Cell A1. Then I skip Cell A2 and
type in the info in Cell A3. In this example I am not supposed to skip A2
and type in the info in A3.

Can we write a code to check and prompt the users not to skip the cell(s)?
Please consider sometimes the users may skip more than one cell, and I only
want to check and prompt the users for the skip cells. The users may or may
not type in the info in all A1:A50. Therefore, the checking should stop on
the last cell of info entered.

Please help. Thanks.

Happy New Year to you all!!!

[Tom Ogilvy]

right click on the sheet tab and select view code. then paste in code like
this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A2:A50")) Is Nothing Then
If IsEmpty(Target.Offset(-1, 0)) Then
Target.Offset(-1, 0).Select
End If
End If
End Sub

--
Regards,
Tom Ogilvy


"AccessHelp" <[email protected]> wrote in message
news:[email protected]...
> I have a range (A1:A50) where users type in the information. In the range
> users not suppose to skip a cell(s) and type in the information in the
cell
> after. For example, I type in the info in Cell A1. Then I skip Cell A2
and
> type in the info in Cell A3. In this example I am not supposed to skip A2
> and type in the info in A3.
>
> Can we write a code to check and prompt the users not to skip the cell(s)?
> Please consider sometimes the users may skip more than one cell, and I
only
> want to check and prompt the users for the skip cells. The users may or
may
> not type in the info in all A1:A50. Therefore, the checking should stop
on
> the last cell of info entered.
>
> Please help. Thanks.
>
> Happy New Year to you all!!!

[AccessHelp]

Hi Tom,

Thanks for the code. I need something a little different. This is what
happens

I have a button on the sheet where users can click on. When the users click
on the button, the macro will check a series of things on the sheet. During
checking, if there is a discrepancy, it will prompt the user with a message
and the user will keep receiving the message until it fixes. This code will
be a part of what I already have.

Thanks again.

"Tom Ogilvy" wrote:

> right click on the sheet tab and select view code. then paste in code like
> this:
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> If Target.Count > 1 Then Exit Sub
> If Not Intersect(Target, Range("A2:A50")) Is Nothing Then
> If IsEmpty(Target.Offset(-1, 0)) Then
> Target.Offset(-1, 0).Select
> End If
> End If
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
>
> "AccessHelp" <[email protected]> wrote in message
> news:[email protected]...
> > I have a range (A1:A50) where users type in the information. In the range
> > users not suppose to skip a cell(s) and type in the information in the
> cell
> > after. For example, I type in the info in Cell A1. Then I skip Cell A2
> and
> > type in the info in Cell A3. In this example I am not supposed to skip A2
> > and type in the info in A3.
> >
> > Can we write a code to check and prompt the users not to skip the cell(s)?
> > Please consider sometimes the users may skip more than one cell, and I
> only
> > want to check and prompt the users for the skip cells. The users may or
> may
> > not type in the info in all A1:A50. Therefore, the checking should stop
> on
> > the last cell of info entered.
> >
> > Please help. Thanks.
> >
> > Happy New Year to you all!!!
>
>
>

[Tom Ogilvy]

Dim lastUsedCell as Long
Dim rng as Range
Dim rng1 as Range
if isempty(cells(50,1)) then
lastusedcell = cells(51,1).End(xlup).row
else
lastusedcell = 50
end if
set rng = Range(Range("A1"),Range("A" & lastUsedCell))
on Error Resume Next
set rng1 = rng.specialCells(xlBlanks")
On Error goto 0
if not rng1 is nothing then
msgbox "I let you skip cells, but now you need to clean them up" & _
vbNewLine & rng1.Address(0,0)
End if

--
Regards,
Tom Ogilvy


"AccessHelp" <[email protected]> wrote in message
news:[email protected]...
> Hi Tom,
>
> Thanks for the code. I need something a little different. This is what
> happens
>
> I have a button on the sheet where users can click on. When the users
click
> on the button, the macro will check a series of things on the sheet.
During
> checking, if there is a discrepancy, it will prompt the user with a
message
> and the user will keep receiving the message until it fixes. This code
will
> be a part of what I already have.
>
> Thanks again.
>
> "Tom Ogilvy" wrote:
>
> > right click on the sheet tab and select view code. then paste in code
like
> > this:
> >
> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > If Target.Count > 1 Then Exit Sub
> > If Not Intersect(Target, Range("A2:A50")) Is Nothing Then
> > If IsEmpty(Target.Offset(-1, 0)) Then
> > Target.Offset(-1, 0).Select
> > End If
> > End If
> > End Sub
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "AccessHelp" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have a range (A1:A50) where users type in the information. In the
range
> > > users not suppose to skip a cell(s) and type in the information in the
> > cell
> > > after. For example, I type in the info in Cell A1. Then I skip Cell
A2
> > and
> > > type in the info in Cell A3. In this example I am not supposed to ski
p A2
> > > and type in the info in A3.
> > >
> > > Can we write a code to check and prompt the users not to skip the
cell(s)?
> > > Please consider sometimes the users may skip more than one cell, and I
> > only
> > > want to check and prompt the users for the skip cells. The users may
or
> > may
> > > not type in the info in all A1:A50. Therefore, the checking should
stop
> > on
> > > the last cell of info entered.
> > >
> > > Please help. Thanks.
> > >
> > > Happy New Year to you all!!!
> >
> >
> >

[AccessHelp]

Hi Tom,

Thanks for the code. Sorry for not getting back to you soon! I just tried
your code, and the code didn't work. When I debug the code, it didn't do
anything. Should we use the FOR statement to check the blank rows?

Please help.

Thanks again.

"Tom Ogilvy" wrote:

> Dim lastUsedCell as Long
> Dim rng as Range
> Dim rng1 as Range
> if isempty(cells(50,1)) then
> lastusedcell = cells(51,1).End(xlup).row
> else
> lastusedcell = 50
> end if
> set rng = Range(Range("A1"),Range("A" & lastUsedCell))
> on Error Resume Next
> set rng1 = rng.specialCells(xlBlanks")
> On Error goto 0
> if not rng1 is nothing then
> msgbox "I let you skip cells, but now you need to clean them up" & _
> vbNewLine & rng1.Address(0,0)
> End if
>
> --
> Regards,
> Tom Ogilvy
>
>
> "AccessHelp" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Tom,
> >
> > Thanks for the code. I need something a little different. This is what
> > happens
> >
> > I have a button on the sheet where users can click on. When the users
> click
> > on the button, the macro will check a series of things on the sheet.
> During
> > checking, if there is a discrepancy, it will prompt the user with a
> message
> > and the user will keep receiving the message until it fixes. This code
> will
> > be a part of what I already have.
> >
> > Thanks again.
> >
> > "Tom Ogilvy" wrote:
> >
> > > right click on the sheet tab and select view code. then paste in code
> like
> > > this:
> > >
> > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > If Target.Count > 1 Then Exit Sub
> > > If Not Intersect(Target, Range("A2:A50")) Is Nothing Then
> > > If IsEmpty(Target.Offset(-1, 0)) Then
> > > Target.Offset(-1, 0).Select
> > > End If
> > > End If
> > > End Sub
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "AccessHelp" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I have a range (A1:A50) where users type in the information. In the
> range
> > > > users not suppose to skip a cell(s) and type in the information in the
> > > cell
> > > > after. For example, I type in the info in Cell A1. Then I skip Cell
> A2
> > > and
> > > > type in the info in Cell A3. In this example I am not supposed to ski
> p A2
> > > > and type in the info in A3.
> > > >
> > > > Can we write a code to check and prompt the users not to skip the
> cell(s)?
> > > > Please consider sometimes the users may skip more than one cell, and I
> > > only
> > > > want to check and prompt the users for the skip cells. The users may
> or
> > > may
> > > > not type in the info in all A1:A50. Therefore, the checking should
> stop
> > > on
> > > > the last cell of info entered.
> > > >
> > > > Please help. Thanks.
> > > >
> > > > Happy New Year to you all!!!
> > >
> > >
> > >
>
>
>