Find method *RESOLVED*

[Sathyaish]

I wish to find out if in any of the cells in any of the rows from column A to cell S have any text or not. Meaning I need a substitute for the UsedRange. I tried the Find method and it worked perfectly for me, until I discovered yesterday that it had to be customized.

The picture is, when my program loads the spreadsheet, it writes some application data (like its own path, this data is not to be seen or used by the user of the spreadsheet) in cells V1 to V4. The column V is made to hide by setting its column width to 0. The user types in text in columns B through S. Even column A is hidden for the same reason as applicable for column V. Now when I called the Find method like this:

Cells(Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row, Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column).Row

it always returned me the fourth Row when there was no data entered by the user, since column V always has application data from cells V1 to V4.

I wish to find which is the largest row out of the range A to S, that has text in it. Thereby I can then determine only out of the select range whether the user has entered invalid data or left out a mandatory field.

I tried customizing the arguments like this but it always returns to me row number 1.

Sheet1.Cells(Sheet1.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row, Sheet1.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column).Row

or even like this

Sheet1.Range(GetColumnAlphabetFromIndex(EquipmentID) & ":" & GetColumnAlphabetFromIndex(NumInvoices)).Find("*").Row 

Public Enum SpreadsheetColumns
    EquipmentID = 0
    Supplier = 1
    Manufacturer = 2
    ModelNumber = 3
    LocationDept = 4
    SerialNumber = 5
    AcquisitionTerms = 6
    LeaseEndDate = 7
    PurchaseDate = 8
    PurchasePrice = 9
    Depreciation = 10
    IsColorDevice = 11
    PagesPrintedPerMonth = 12
    AmountSupplies = 13
    AmountServices = 14
    CPPTotal = 15
    CPPSupplies = 16
    CPPService = 17
    NumInvoices = 18
    ApplicationData = 21
    AllColumns = 65535
End Enum

Public Function GetColumnAlphabetFromIndex(ByVal ZeroBasedColumnIndex As SpreadsheetColumns) As String

If ZeroBasedColumnIndex < 0 Or ZeroBasedColumnIndex > 21 Then Exit Function

    GetColumnAlphabetFromIndex = Choose _
    (ZeroBasedColumnIndex + 1, _
    "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V")
        
End Function

Please help me.




THIS ISSUE HAS BEEN RESOLVED

http://www.visualbasicforum.com/showthread.php?p=489177