Finding the last used cell in a spreadsheet [dupli

Question

This question already has an answer here:

• Error in finding last used cell in Excel with VBA 12 answers

I need ur help to automate one MIS Report.

Description: Daily we will circulate MIS by downloading the open items lying in GL. Total no of line items will vary on daily basis. I have created a macro where i have selected Range from first item to last one(5800 Line items). But on day 2 it would increase to 6500 as i have only selected range as 5800..system is showing an error. Instead of specific range selection can we fix some thing like 10000 Items ? or System should automatically select the entire range based on the number of transactions.

Range("C5881").Select

Answer 1

If you DELETE the rows in your sheet before the download UsedRange() should return what you are looking for after the download. Note that you cannot just delete the contents of the previous days rows, the rows need to be deleted. The link in the comment shows a way to find the "last row" if you just delete the contents of the cells leaving empty rows behind.

Here's some test code using Siddharth's method and UsedRange:

Sub x()
With Sheets("Sheet1")
    Debug.Print .UsedRange.Address
    Debug.Print .UsedRange.Cells.Count
    If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
        lastrow = .Cells.Find(What:="*", _
                      After:=.Range("A1"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious, _
                      MatchCase:=False).Row
    Else
        lastrow = 1
    End If
    Debug.Print lastrow
    .UsedRange.Select
End With

End Sub

Note that UsedRange() will count cells with formatting and cells that are empty. Add some cells to sheet1 and run the code. Delete the contents of cells in the bottom rows and see how the result change.

Answer 2

I got the answer

By using the below code i am able to get the result...i would like to thank one and all who reponsed on the thread

Dim wksSheet as worksheet Dim rng as Range

With wksSheet
    LastRow = .Range("A1").End(xlDown).Row
    LastCol = .Range("A1").End(xlToRight).Column
    Set rng = .Range("A1", .Cells(LastRow, LastCol))
End With

    LastRow = .Range("A1").End(xlDown).Row  or        LastRow = .Range("A1048576").End(xlUp).Row