Checking Blank Rows
 

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!!!

Checking Blank Rows
 

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" wrote in message
...
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!!!

Checking Blank Rows
 

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" wrote in message
...
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!!!

Checking Blank Rows
 

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" wrote in message
...
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" wrote in message
...
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!!!

Checking Blank Rows
 

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" wrote in message
...
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" wrote in message
...
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!!!