Results 1 to 4 of 4

LastRow = ActiveSheet.UsedRange.Rows.Count not givng correct value

Threaded View

BobBlooms LastRow =... 08-24-2012, 11:52 AM
wallyeye Re: LastRow =... 08-24-2012, 01:24 PM
BobBlooms Re: LastRow =... 08-24-2012, 02:01 PM
Cutter Re: LastRow =... 08-24-2012, 02:02 PM
  1. #1
    Registered User
    Join Date
    07-10-2012
    Location
    FL
    MS-Off Ver
    Excel 2007
    Posts
    17

    LastRow = ActiveSheet.UsedRange.Rows.Count not givng correct value

    The ojective of this routine is to determine if any employees are overdue in their salary review date. The routine first goes out to an Acess DB, and grabs the records where the salary consideration date is < Now(). If LastRow count is > 1 (title line) a msgbox is activated. LastRow value keeps coming up 16 even when I delete the rows below the title row. I am truly lost on this.

    Option Explicit
    
    Sub Import_AccessData()
    '   Set a reference to Microsoft ADO x.x library using Tools | Reference... in the VB-editor.
    
    Dim cnt As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim stDB As String
    Dim wsTitles As Worksheet
    Dim wsSheet As Worksheet
    Dim lnNumberOfField As Long, lnCount As Integer
    Dim LastRow As Integer
    
    Set wsSheet = ThisWorkbook.Worksheets("Sheet1")
    Set wsTitles = ThisWorkbook.Worksheets("Sheet1")
    
    stDB = ThisWorkbook.Path & "\" & "EOHHS.mdb"
    
    wsSheet.Range("A1").CurrentRegion.Clear
    
    cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & stDB & ";"
    
    rst.Open "SELECT * FROM qSal_Con_Date_Has_Passed", cnt
    
    lnNumberOfField = rst.Fields.Count
    
    For lnCount = 0 To lnNumberOfField - 1
    wsTitles.Cells(1, lnCount + 1).Value = rst.Fields(lnCount).Name
    Next lnCount
    
    wsSheet.Cells(2, 1).CopyFromRecordset rst
    
    Set rst = Nothing
    Set cnt = Nothing
       
    Sheets("Sheet1").Select
    Range("A1").Select
    
    LastRow = ActiveSheet.UsedRange.Rows.Count
        
    If LastRow > 1 Then
        MsgBox "Salary consideration action is required on " & LastRow & " employees"
    Else: End If
          
    End Sub
    Last edited by Cutter; 08-24-2012 at 02:01 PM. Reason: Added code tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1