Counting until end of excel sheet

Hi,

I was windering if there is a way to stop a loop when it reaches the end of an excel sheets data rows i.e if there is 3000 rows the loop will only run 3000 times.

Thanks

C19

Guys,

I have this code but is there anything more efficient....

VB Code:

Sub exportMargins()
 
 Dim acbsDB As Database, marginsRS As Recordset, r As Long, RecordNumber As Long
    Dim queryNo As Double
    Set acbsDB = OpenDatabase("S:\Hockney Folder Structure\CUSTOMER SERVICE\Michael\ACBS\ACBS.mdb")
     ' open the database
    Set marginsRS = acbsDB.OpenRecordset("Margins")
      
     ' get all records in a table
    r = 2 ' the start row in the worksheet
Do While Sheet1.Range("A" & r).Value <> ""
    
    
    With marginsRS
        .AddNew ' create a new record
         ' add values to each field in the record
        RecordNumber = .Fields(0)
        .Fields("Portfolio") = Sheet1.Range("A" & r).Value
        .Fields("Cost_Centre_Number") = Sheet1.Range("B" & r).Value
        .Fields("ACBS_Customer_Name") = Sheet1.Range("C" & r).Value
        .Fields("Credit_Arrangement_Number") = Sheet1.Range("D" & r).Value
        .Fields("Loan_Number") = Sheet1.Range("E" & r).Value
        .Fields("Product_Group_Description") = Sheet1.Range("F" & r).Value
        .Fields("Currency") = Sheet1.Range("G" & r).Value
        .Fields("Interest_Income") = Sheet1.Range("H" & r).Value
        .Fields("Base_Currency_Equiv_Interest_Income") = Sheet1.Range("I" & r).Value
        .Fields("Interest_Expense") = Sheet1.Range("J" & r).Value
        .Fields("Base_Currency_Interest_Expense") = Sheet1.Range("K" & r).Value
        .Fields("Cummulative_Base_Currency_Margin") = Sheet1.Range("L" & r).Value
        .Fields("Top_Hierarchical_Group") = Sheet1.Range("M" & r).Value
        .Fields("Secondary_Hierarchical_Group") = Sheet1.Range("N" & r).Value
        .Fields("Notes") = Sheet1.Range("O" & r).Value
         ' add more fields if necessary...
        .Update ' stores the new record
        End With
        r = r + 1 ' count the rows to determine if data exists (exit loop)
Loop
 
    marginsRS.Close
    Set marginsRS = Nothing
    acbsDB.Close
    Set acbsDB = Nothing
End Sub

you can use the equivalent of pressing end then up from cell A65500

Some thing like

Code:

---

lngLastRow=sht.cells(65500,1).End(xlUp).Row

---

This assumes no entries have been made beneath the last data cell used in column 1 (A).

Or you could go from the top down, but the top down method may take you to the bottom of the sheet

Code:

---

lngLastRow=sht.cells(1,1).End(xlDown).Row

---

This one assumes continuous data in column 1 (A) from the title/data line in row 1.


hope that helps a bit.

Hi,

Thanks - looks a lot cleaner.

Cheers

C19

I guess you've figured out by now that there are 65,536 rows max in an Excell spreadsheet.

I think that this function from Vince is what you were looking for if you want to universally calculate the number (but 65,536 has been the actual number for a long time now) ... with a Message Box to show the value of the function:

Code:

---

MsgBox ActiveWorkbook.ActiveSheet.Cells(1, 1).End(xlDown).Row

---

I would personally use the UsedRange property of the sheet:

VB Code:

Private Sub LoopUntilEnd()
 
    Dim oRange As Range, lRow As Long
    
    Set oRange = Application.ActiveSheet.UsedRange
    
    'Loop through all rows
    For lRow = 1 To oRange.Rows.Count
        'Do whatever
        Debug.Print lRow
    Next lRow
 
    Set oRange = Nothing
 
End Sub

I think the original poster wanted to be sure he didn't roll off the bottom of the Worksheet as he added rows. (???)

Comintern:
Used range usually works ok.

But if you add a couple of extra lines and delete the data out, the used range stays larger than the data is so may throw off the counter. :)

Whichever way the original poster decides to do... hope it works. Also if the OP has the time, try all methods so you can compare.

Ummmmm ... Vince ... if your last line was added at the bottom of the sheet (row 65,536 or whatever the bottom row calculates out to be ... 65,536) ... it's time to stop adding lines to the sheet and maybe open a new sheet or switch to Access.

Where is the Original Poster? He could put this all to rest.

Sorry ... I am in outer space ... I concede that the OP was looking for the actual used range. Pardon my dumb flailing.

Also, to determine the last used row...

VB Code:

MsgBox Workbooks(1).Sheets(1).Cells.SpecialCells(xlCellTypeLastCell).Row