Find Last Row In A Group Of Columns

Question

I have a userform that enters data into columns I to S but sometimes all data points does not need to be entered. My probably is I only have the last row counting up on column I so if I have data in J through S, they would get replaced with the next set of data that has data in column I.

What I need help is coding for the last row of all columns or the next blank row of all columns. Thanks.

My code:

 Private Sub cmd_EnterData_Click()

 Dim iRow As Long
 Dim Lastrow As Long
 Dim ws As Worksheet
 Set ws = Worksheets("FirstShift")

 Lastrow = ws.Range("i101").End(xlUp).Row
 'find first empty row in database
 For iRow = 16 To Lastrow
  If ws.Cells(iRow, 9) = "" And ws.Cells(iRow, 10) = "" Then
    ws.Cells(iRow, 9).Value = Me.textbox_Lane1.Value
    ws.Cells(iRow, 10).Value = Me.textbox_Lane2.Value
    ws.Cells(iRow, 11).Value = Me.textbox_Lane3.Value
    ws.Cells(iRow, 12).Value = Me.textbox_Lane4.Value
    ws.Cells(iRow, 13).Value = Me.textbox_Lane5.Value
    ws.Cells(iRow, 14).Value = Me.textbox_Lane6.Value
    ws.Cells(iRow, 15).Value = Me.textbox_Lane7.Value
    ws.Cells(iRow, 16).Value = Me.textbox_Length.Value
    ws.Cells(iRow, 17).Value = Me.textbox_SheetCount.Value
    ws.Cells(iRow, 18).Value = Me.cbchecktype.Value
    ws.Cells(iRow, 19).Value = Me.cbchecktype1.Value
  End If
 Next iRow


 If checkbox_Retest.Value = False And Me.textbox_Lane1.Value = "" Then
 'do nothing
 Me.textbox_Lane1.SetFocus
 MsgBox "ENTER LANE 1 WIDTH!"
 Exit Sub
 End If

 If checkbox_Retest.Value = False And Me.textbox_Length.Value = "" Then
 'do nothing
 Me.textbox_Length.SetFocus
 MsgBox "ENTER YOUR LENGTH!"
 Exit Sub
 End If

 If checkbox_Retest.Value = False And Me.textbox_SheetCount.Value = "" Then
 'do nothing
 Me.textbox_SheetCount.SetFocus
 MsgBox "ENTER THE SHEETCOUNT!"
 Exit Sub
 End If

 If checkbox_Retest.Value = False And Me.cbchecktype.Value = "" Then
 'do nothing
 Me.cbchecktype.SetFocus
 MsgBox "ENTER 'PASS' OR 'FAIL' FOR PERF CHECK!!"
 Exit Sub
 Select Case checktype
    Case Trim(Me.cbchecktype.Value) = "PASS"
    checktype = "PASS"

    Case Trim(Me.cbchecktype.Value) = "FAIL"
    checktype = "FAIL"
 End Select
 End If

 If checkbox_Retest.Value = False And Me.cbchecktype1.Value = "" Then
 'do nothing
 Me.cbchecktype1.SetFocus
 MsgBox "ENTER 'PASS' OR 'FAIL' FOR SLITHER CHECK!!"
 Exit Sub
 Select Case checktype1
    Case Trim(Me.cbchecktype1.Value) = "PASS"
    checktype1 = "PASS"

    Case Trim(Me.cbchecktype1.Value) = "FAIL"
    checktype1 = "FAIL"
 End Select
 End If

 With ws
.Unprotect Password:="password"
.Cells(iRow, 9).Value = Me.textbox_Lane1.Value
.Cells(iRow, 10).Value = Me.textbox_Lane2.Value
.Cells(iRow, 11).Value = Me.textbox_Lane3.Value
.Cells(iRow, 12).Value = Me.textbox_Lane4.Value
.Cells(iRow, 13).Value = Me.textbox_Lane5.Value
.Cells(iRow, 14).Value = Me.textbox_Lane6.Value
.Cells(iRow, 15).Value = Me.textbox_Lane7.Value
.Cells(iRow, 16).Value = Me.textbox_Length.Value
.Cells(iRow, 17).Value = Me.textbox_SheetCount.Value
.Cells(iRow, 18).Value = Me.cbchecktype.Value
.Cells(iRow, 19).Value = Me.cbchecktype1.Value
.Protect Password:="password"
End With

'clear the data
Me.textbox_Lane1.Value = ""
Me.textbox_Lane2.Value = ""
Me.textbox_Lane3.Value = ""
Me.textbox_Lane4.Value = ""
Me.textbox_Lane5.Value = ""
Me.textbox_Lane6.Value = ""
Me.textbox_Lane7.Value = ""
Me.textbox_Length.Value = ""
Me.textbox_SheetCount.Value = ""
Me.cbchecktype.Value = ""
Me.cbchecktype1.Value = ""
Me.checkbox_Retest.Value = False

 Me.Hide

End Sub

A.S.H, I've tried your code and same thing is happening BUT I don't think its the code. Please see pictures of before and after. I think the problem is my IF STATEMENT:

"If ws.Cells (iRow, 9) = "" And ws.Cells(iRow, 10) = "" Then

As you can see, the function works fine when I have an item in columns 9 (I) and 10 (J), but when I put no data in those two columns then it gets replaced with whatever data I've entered on my userform as long as it includes data in columns 9 or 10..Thoughts on correcting this?

Answer 1

The following gets you the last non-empty row considering all columns:

Lastrow = ws.UsedRange.Find("*", , , , xlByRows, xlPrevious).Row

You can also restrict it to a set of columns, by replacing .UsedRange to the specific columns range, i.e. the following gets you the last non-empty row in columns G to AB:

Lastrow = ws.Range("G:AB").Find("*", , , , xlByRows, xlPrevious).Row

Answer 2

Add these two dim statements.

Dim ColumnCount As Integer
Dim x As Long 

Change your code:

Lastrow = ws.Range("i101").End(xlUp).Row

to:

Lastrow = 0

For ColumnCount = 0 To 10
    x = ws.Range("I101").Offset(0, ColumnCount).End(xlUp).Row
    If x > Lastrow Then Lastrow = x
Next ColumnCount

edit: This for loop always start on 16. Is it maybe supposed to start on Lastrow+1?

For iRow = 16 To Lastrow