Current code only skips up one cell, want to skip up to next empty cell

[Kusaywa]

Currently, in Column B, if user attempts to skip a row he is prompted not to skip, acknowledges and it skips up one row. Problem is, if last entry was in row 10, and they click on row 400, they have to acknowledge the prompt 390 times. Any way to skip up to next empty cell in Column B? Thanks

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 CurrentRow = ActiveCell.Row
If CurrentRow = 1 Then Exit Sub
 CurrentCol = ActiveCell.Column
If Cells(CurrentRow - 1, CurrentCol).Value = 0 Then
 MsgBox ("Please Do Not Skip Rows")
 ActiveCell.Offset(-1, 0).Activate
End If

End Sub

 

[shinigamilight]

Try this code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        On Error Resume Next
        If Target.Row = 1 Then Exit Sub
        
        If Target.Offset(-1, 0).Value = "" Then
            MsgBox "Please Do Not Skip Rows"
             ActiveSheet.Range(Cells(1, Target.Column), Cells(Rows.Count, Target.Column)).Find("*", , xlFormulas, , , xlPrevious).Offset(1, 0).Select
        End If

End Sub

 

[Kusaywa]

It works but for some reason worksheet opens up to last row (in my case 4800). Whereas previously it opened up to first empty row.

 

[dmt32]

Hi,
see if this update to your code does what you want

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static msgOff   As Boolean
   
    CurrentRow = ActiveCell.Row
    If CurrentRow = 1 Then Exit Sub
    CurrentCol = ActiveCell.Column
   
    If Len(Cells(CurrentRow - 1, CurrentCol).Value) = 0 Then
        If Not msgOff Then MsgBox "Please Do Not Skip Rows", 48, "Blank Rows": msgOff = True
        ActiveCell.Offset(-1, 0).Activate
    Else
        msgOff = False
    End If
   
End Sub

Dave

 

[DanteAmor]

Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Row < 3 Then Exit Sub
  If ActiveCell.Offset(-1).Value = "" Then
    MsgBox "Please Do Not Skip Rows"
    Cells(Rows.Count, ActiveCell.Column).End(3)(2).Select
  End If
End Sub

 

[Kusaywa]

Still getting mixed results although shinigamilight was the closest to what I want.
I'm afraid I left out part of the code so maybe that is what is causing the problem?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 CurrentRow = ActiveCell.Row
If CurrentRow = 1 Then Exit Sub
 CurrentCol = ActiveCell.Column
If Cells(CurrentRow - 1, CurrentCol).Value = 0 Then
 MsgBox ("Please Do Not Skip Rows")
 ActiveCell.Offset(-1, 0).Activate
End If

Dim MyRange As Range, lr As Long
lr = Cells(Rows.Count, "B").End(xlUp).Row
Set MyRange = Range("B2:B" & lr)
For Each cell In MyRange
If cell.Value <> "" And cell.Offset(0, 2).Value = "" Then
MsgBox "Your ID Number is Required"
Application.EnableEvents = False
cell.Offset(0, 2).Select
Application.EnableEvents = True
Exit Sub
End If
Next cell



End Sub

 

[Kusaywa]

Ok, got it!
This is what I used.
Thanks everyone for the assist!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 CurrentRow = ActiveCell.Row
If CurrentRow = 1 Then Exit Sub
 CurrentCol = ActiveCell.Column
If Cells(CurrentRow - 1, CurrentCol).Value = 0 Then
 MsgBox ("Please Do Not Skip Rows")
 Cells(Rows.Count, ActiveCell.Column).End(3)(2).Select
End If
End Sub

 

[DanteAmor]

Cells(Rows.Count, ActiveCell.Column).End(3)(2).Select

Can you explain to me what was wrong with my code from post #5, that is, in what case did it not return the expected result?

 

[Kusaywa]

Yours also would open to the last cell (4800). Clicking ok did return it to the first open cell but I have other code which zooms in on sheet as well as hiding the ribbon among other things, basically making the sheet tamper proof. For some reason it would zoom in on only columns A & B. Mine, with help from you, opens to the first empty cell in column B.

 

[DanteAmor]

Yours also would open to the last cell (4800).

What do you mean by "open to the last cell (4800)"
If you mean open the file.
In your Open event you can select cell A1 or cell B1 or last cell with data on column B.

With your code from post #7, if you select a column where you don't have data, the msgbox appears twice, which doesn't happen with my code.

For some reason it would zoom in on only columns A & B. Mine, with help from you, opens to the first empty cell in column B.

Nowhere in your code do I see mention of column B.

I see problems with your code and redundancy, but if it works for you...