none
VBA code within Excel to find the data from the row selected RRS feed

  • Question

  • Was wondering if anyone would be able to tell me where my code is not staying true to what it is supposed to be doing.  I created a function that I use for selecting the row I am working on and pull the data from different columns in that row.  It works find for the first 3 rows and then stops working.  Sometimes it will work for the first 20 rows and then stops working properly.  Just weird that it works when it feels like it.  Wondering if anyone has suggestions.  Thank you.  
    Function FindLastRowNbr() As Double
        Dim LastCell As Boolean
        Dim CurRowNbr As Double
    
        LastCell = False
    
        Range("A3").Select
        CurRowNbr = 3
        Do While Not LastCell
            If Application.WorksheetFunction.Trim(ActiveCell.Offset(CurRowNbr, 1)) = "" Then
                LastCell = True
            Else
                CurRowNbr = CurRowNbr + 1
            End If
        Loop
    
        FindLastRowNbr = CurRowNbr
    
        Exit Function
    End Function


    • Edited by WandaM Wednesday, April 5, 2017 9:37 PM
    Wednesday, April 5, 2017 9:37 PM

Answers

  • Because you're using Offset, the first cell being evaluated is the one that's 3 rows down and 1 column to the right of the active cell, which in this case is B6.  I'm assuming that this is not what you want.  If your intent is to start at A3, you should replace...

    ActiveCell.Offset(CurRowNbr, 1)

    with

    Cells(CurRowNbr, 1)

    Also, there's no need to select A3 first, since you're assigning the starting row number to CurRowNbr.  So you can delete this line...

    Range("A3").Select

    Also, you can declare CurRowNbr as Long, instead of Double.  Same thing with your function.  You can declare it as returning a Long data type.

    Also, if your column contains blank/empty cells within your data, you can find the last row in Column A as follows...

        Dim LastRow As Long
        
        LastRow = Cells(Rows.Count, "A").End(xlUp).Row

    As you can see, it starts searching from the bottom up, and without looping.

    Hope this helps!


    Domenic Tamburino
    Microsoft MVP - Excel
    xl-central.com - "For Your Microsoft Excel Solutions"


    • Edited by Domenic Tamburino Wednesday, April 5, 2017 10:25 PM
    • Marked as answer by WandaM Thursday, April 6, 2017 2:55 AM
    Wednesday, April 5, 2017 10:23 PM

All replies

  • Because you're using Offset, the first cell being evaluated is the one that's 3 rows down and 1 column to the right of the active cell, which in this case is B6.  I'm assuming that this is not what you want.  If your intent is to start at A3, you should replace...

    ActiveCell.Offset(CurRowNbr, 1)

    with

    Cells(CurRowNbr, 1)

    Also, there's no need to select A3 first, since you're assigning the starting row number to CurRowNbr.  So you can delete this line...

    Range("A3").Select

    Also, you can declare CurRowNbr as Long, instead of Double.  Same thing with your function.  You can declare it as returning a Long data type.

    Also, if your column contains blank/empty cells within your data, you can find the last row in Column A as follows...

        Dim LastRow As Long
        
        LastRow = Cells(Rows.Count, "A").End(xlUp).Row

    As you can see, it starts searching from the bottom up, and without looping.

    Hope this helps!


    Domenic Tamburino
    Microsoft MVP - Excel
    xl-central.com - "For Your Microsoft Excel Solutions"


    • Edited by Domenic Tamburino Wednesday, April 5, 2017 10:25 PM
    • Marked as answer by WandaM Thursday, April 6, 2017 2:55 AM
    Wednesday, April 5, 2017 10:23 PM
  • Hi WandaM,

    Domenic Tamburino already informed you what you did wrong in your code.

    further , if I can see the title of the thread. then I can see that you want to find the data from the selection.

    you can try to use code below.

    Sub demotest()
    Dim MyPlage, cell As Range
    Set MyPlage = Selection
    For Each cell In MyPlage
    Select Case cell.Value
    Case Is = "demo"
    cell.Interior.ColorIndex = 10
    Debug.Print (cell.Value)
    End Select
    Next
    End Sub

    Output:

    you can modify the code as per your requirement.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 6, 2017 2:11 AM
    Moderator
  • Can't thank you enough.  This worked like a charm.  Not sure why mine was so flaky .... it had a mind of its own.  Again thank you and I understand what the code is doing.  Have a wonderful day.
    Thursday, April 6, 2017 2:55 AM