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!

Register Yourself Login