VBA Select LastRow ignoring formulas

[Sipowicz]

Good morning/afternoon all,

I am currently using the following VBA code. The data entry are from column A to G and some formulas are in column H and I.
The code for the data entry is working perfectly, however I would like to modify the code in order to find the last available row by ignoring everything that is after column G.
In order words, the code of the data entry has to be focused only between column A to G.

If you have any tip on how I could modify the code, that would be amazing. Thank you in advance for your suggestions!

'Variable Declaration
Dim BlnVal As Boolean

Private Sub UserForm_Initialize()
    'Variable declaration
    Dim IdVal As Integer
   
    'Finding last row in the Data Sheet
    IdVal = fn_LastRow(Sheets("Data"))
   
    'Update next available id on the userform
    frmData.txtId = IdVal
End Sub





Sub cmdAdd_Click()
    On Error GoTo ErrOccured
    'Boolean Value
    BlnVal = 0
   
    'Data Validation
    Call Data_Validation
   
    'Check validation of all fields are completed are not
    If BlnVal = 0 Then Exit Sub
     
    'TurnOff screen updating
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    'Variable declaration
    Dim txtId, txtDate, txtGender, txtLocation, txtCNum, txtEAddr, txtRemarks
    Dim iCnt As Integer
   
    'find next available row to update data in the data worksheet
    iCnt = fn_LastRow(Sheets("Data")) + 1
   
   
    'Update userform data to the Data Worksheet
    With Sheets("Data")
        .Cells(iCnt, 1) = iCnt - 1
        .Cells(iCnt, 2) = frmData.txtDate
        .Cells(iCnt, 3) = frmData.txtGender
        .Cells(iCnt, 4) = frmData.txtLocation.Value
        .Cells(iCnt, 5) = frmData.txtEAddr
        .Cells(iCnt, 6) = frmData.txtCNum
        .Cells(iCnt, 7) = frmData.txtRemarks
     
       
        'Diplay headers on the first row of Data Worksheet
        If .Range("A1") = "" Then
            .Cells(1, 1) = "Sell ID"
            .Cells(1, 2) = "Date of Sell"
            .Cells(1, 3) = "Gender"
            .Cells(1, 4) = "Location"
            .Cells(1, 5) = "Email Addres"
            .Cells(1, 6) = "Contact Number"
            .Cells(1, 7) = "Remarks"
           
            'Formatiing Data
            .Columns("A:G").Columns.AutoFit
            .Range("A1:G1").Font.Bold = True
            .Range("A1:G1").LineStyle = xlDash
           
        End If
    End With
   
    'Display next available Id number on the Userform
    'Variable declaration
    Dim IdVal As Integer
   
    'Finding last row in the Data Sheet
    IdVal = fn_LastRow(Sheets("Data"))
   
    'Update next available id on the userform
    frmData.txtId = IdVal
   
ErrOccured:
    'TurnOn screen updating
    Application.ScreenUpdating = True
    Application.EnableEvents = True
   
End Sub





Function fn_LastRow(ByVal Sht As Worksheet)

    Dim lastRow As Long
    lastRow = Sht.Cells.SpecialCells(xlLastCell).Row
    lRow = Sht.Cells.SpecialCells(xlLastCell).Row
    Do While Application.CountA(Sht.Rows(lRow)) = 0 And lRow <> 1
        lRow = lRow - 1
    Loop
    fn_LastRow = lRow

End Function

 

[Fluff]

Hi & welcome to MrExcel.
How about

Function fn_LastRow(ByVal Sht As Worksheet)

    Dim lastRow As Long
    lastRow = Sht.Range("A:G").Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row
    If lastRow = 1 Then
      fn_LastRow = 2
    Else
      fn_LastRow = lastRow
    End If
End Function

 

[Sipowicz]

Hi & welcome to MrExcel.
How about

Function fn_LastRow(ByVal Sht As Worksheet)

    Dim lastRow As Long
    lastRow = Sht.Range("A:G").Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row
    If lastRow = 1 Then
      fn_LastRow = 2
    Else
      fn_LastRow = lastRow
    End If
End Function

Running smoothly! Thank you so much for taking some of your time to reply

 

[Fluff]

You're welcome & thanks for the feedback.