Autofill Until No More Adjacent Cell Values

  • SOLVED
    I need to make the following code work in a range that could vary... b2:b2 or b2:b#### depending on the number of results.. then I want to add this to the second snippet so it will run as one macro.


    Code
    Range("B2").Select
        ActiveCell.FormulaR1C1 = "=MID(RC[1],34,20)"
        Range("B2").Select
        Selection.AutoFill Destination:=Range("B2:B101")
        Range("B2:B101").Select
        Columns("B:B").EntireColumn.AutoFit
        Range("B1").Select
        Range("A1:D101").Sort Key1:=Range("B2"), Order1:=xlAscending, Header:= _
            xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
  • Re: Combine Macro And Autofill Row


    Not sure what you're asking, but see if this helps:

    Code
    Dim lLastRow As Long
    lLastRow = 101 'how do you determine last row???
    Range(Cells(2, "B"), Cells(lLastRow, "B")).FormulaR1C1 = "=MID(RC[1],34,20)"
    Columns("B:B").EntireColumn.AutoFit
    Range(Cells(2, "A"), Cells(lLastRow, "D")).Sort Key1:=Range("B2"), Order1:=xlAscending, Header:= _
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal


    Also, are you really using Excel 2007, because I didn't think FileSearch was compatible with that version?

  • Re: Macro Autofill Row until no more adjacent values


    I use 2000 2003 and 2007, I edited my profile to reflect this :)
    I edited my first post because I forgot to make this '1 question per thread'


    Sorry I missed that should have explained, currently my search will yield 100 results (plus the header) bringing me to 101... I recorded the macro and that is number it left with...


    This number will continue to grow, which is one problem I am having.. how do I tell it to keep looking until there is no information in the adjacent cells?


    I use another macro that was given with the help of this forum that goes as follows

    Code
    For intLstRow = Range("E" & Rows.Count).End(xlUp).Row To 2 Step -1
            With Range("E" & intLstRow)
                If .Value >= .Offset(0, 1).Value Then .EntireRow.Delete


    I tried converting that to do what I want in the other code but can not figure it out

  • Re: Macro Autofill Row until no more adjacent values


    If by "adjacent cells", you mean column A, then use my code above with the following change:

    Code
    lLastRow = Range("A" & Rows.Count).End(xlUp).Row

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!