VBA Select LastRow ignoring formulas

Sipowicz

New Member
Joined
Apr 13, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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!


VBA Code:
'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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
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

New Member
Joined
Apr 13, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,386
Messages
5,636,012
Members
416,892
Latest member
Bensch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top