Need to modify the Find next blank Row code.

[Javi]

Hi All,

The below is part of my code that finds the 1st blank Row in column "E".

How can I modify it so that it start the serach for a blank Row at Row 107?

Thank you advice will be appreciated!!!

iRow = ws.Cells(Rows.Count, 5) _
  .End(xlUp).Offset(1, 0).Row

Private Sub CommandButton1_Click()
'Private Sub CommandButton1_Click()
Dim rNextCl As Range
 ' Row count = where the select cell finishes and window box moves to rows over
 ' offset = howmany cells up or down from the row count
 
Set rNextCl = Worksheets("QT").Cells(Rows.Count, 5).End(xlUp).Offset(2, 0)
Worksheets("QT").Activate
rNextCl.Select
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("QT")
    'ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    'ActiveSheet.Unprotect
'Find  first empty row in database Row count is the col it uses
iRow = ws.Cells(Rows.Count, 5) _
  .End(xlUp).Offset(1, 0).Row
'check for a part number
'If Trim(Me.branch.Value) = "" Then
  'Me.branch.SetFocus
  'MsgBox "Please enter a Branch number"
If Me.sapor = "" Or Me.jobna = "" Or Me.ordernu = "" Or Me.snd = "" Or Me.mcode = "" Then
MsgBox ("Feilds SAP Number, Job Name, Price, Code and Month Code Must be Completed")
  Exit Sub
  
  
End If
    ' Enter the range where the data is to be entered
'copy the data to the database
With ws
    ws.Cells(iRow, 4).Value = Me.sapor.Value
    .Cells(iRow, 5).Value = Me.jobna.Value
    .Cells(iRow, 6).Value = Me.ordernu.Value
    .Cells(iRow, 7).Value = Me.edate1.Value
    .Cells(iRow, 20).Value = Me.snd.Value
    .Cells(iRow, 21).Value = Me.mcode.Value
    .Cells(iRow, 44).Value = Me.warr1.Value
    .Cells(iRow, 45).Value = Me.warr2.Value
    .Cells(iRow, 46).Value = Me.warr3.Value
    
    
    End With

'Clear the data when the ADD button is clicked
Me.sapor.Value = ""
Me.jobna.Value = ""
Me.ordernu.Value = ""
Me.snd.Value = ""
Me.mcode.Value = ""
Me.warr1.Value = ""
Me.warr2.Value = ""
Me.warr3.Value = ""
Me.edate1.Value = Format(Date, "mm/dd/yyyy")

 

[Firefly2012]

The current code searches backwards from the last row on the sheet. So you could just check if that returned value is greater than 107:

iRow = ws.Cells(Rows.Count, 5) _
  .End(xlUp).Offset(1, 0).Row

If iRow < 107 Then iRow = 107

 

[jaslake]

Hi Javi
Perhaps something like this

Option Explicit
Sub test()
    Dim irow As Long
    irow = Range("E107").End(xlDown).Offset(1, 0).Row
End Sub

 

[Javi]

Thank you both!! Both worked great!!

 

[jaslake]

You're welcome...glad to be of help.