Search up from last row

Discussion in 'Microsoft Excel Programming' started by Miree, Apr 2, 2009.

  1. Miree

    Miree Guest

    I need to modify my code to search from the last row, at the moment it only
    goes from the last row there is data in for my specific column(DT), can I
    make it start from the last row where there is data from column A but only
    searching the column i need(DT)

    Dim rng As Range
    Dim i As Long

    Set rng = ActiveSheet.Range(Cells(1, "DT"), Cells(Rows.Count, "DT").End(xlUp))

    With rng
    For i = .Rows.Count To 1 Step -1
    If InStr(UCase(.Cells(i)), UCase(UserForm7.TextBox2.Text)) = 0 Then
    .Cells(i).EntireRow.Delete
    End If
    Next i
    End With

    Thank you in advance
     
    Miree, Apr 2, 2009
    #1
    1. Advertisements

  2. Miree

    Jacob Skaria Guest

    Dim lngTemp, lngRow

    lngRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

    For lngTemp = lngRow To 1 Step -1
    If InStr(Range("DT" & lngTemp), UCase(UserForm7.TextBox2.Text)) = 0 Then
    Activesheet.Cells(lngTemp).EntireRow.Delete
    End If
    Next lngTemp

    If this post helps click Yes
    ---------------
    Jacob Skaria


    "Miree" wrote:

    > I need to modify my code to search from the last row, at the moment it only
    > goes from the last row there is data in for my specific column(DT), can I
    > make it start from the last row where there is data from column A but only
    > searching the column i need(DT)
    >
    > Dim rng As Range
    > Dim i As Long
    >
    > Set rng = ActiveSheet.Range(Cells(1, "DT"), Cells(Rows.Count, "DT").End(xlUp))
    >
    > With rng
    > For i = .Rows.Count To 1 Step -1
    > If InStr(UCase(.Cells(i)), UCase(UserForm7.TextBox2.Text)) = 0 Then
    > .Cells(i).EntireRow.Delete
    > End If
    > Next i
    > End With
    >
    > Thank you in advance
     
    Jacob Skaria, Apr 2, 2009
    #2
    1. Advertisements

  3. Miree

    Miree Guest

    sorry this didnt work, what i think might be eaisier(for my simple midnd) is
    if you could help me with a code to identify and select the row one down from
    the last data point in column A

    "Jacob Skaria" wrote:

    > Dim lngTemp, lngRow
    >
    > lngRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    >
    > For lngTemp = lngRow To 1 Step -1
    > If InStr(Range("DT" & lngTemp), UCase(UserForm7.TextBox2.Text)) = 0 Then
    > Activesheet.Cells(lngTemp).EntireRow.Delete
    > End If
    > Next lngTemp
    >
    > If this post helps click Yes
    > ---------------
    > Jacob Skaria
    >
    >
    > "Miree" wrote:
    >
    > > I need to modify my code to search from the last row, at the moment it only
    > > goes from the last row there is data in for my specific column(DT), can I
    > > make it start from the last row where there is data from column A but only
    > > searching the column i need(DT)
    > >
    > > Dim rng As Range
    > > Dim i As Long
    > >
    > > Set rng = ActiveSheet.Range(Cells(1, "DT"), Cells(Rows.Count, "DT").End(xlUp))
    > >
    > > With rng
    > > For i = .Rows.Count To 1 Step -1
    > > If InStr(UCase(.Cells(i)), UCase(UserForm7.TextBox2.Text)) = 0 Then
    > > .Cells(i).EntireRow.Delete
    > > End If
    > > Next i
    > > End With
    > >
    > > Thank you in advance
     
    Miree, Apr 2, 2009
    #3
  4. Jacob gave you that (well, except for the "one down from" part). Jacob
    posted this...

    lngRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

    which is the row containing the last piece of data in Column A. Since you
    want one down from this, just add one to it...

    RowAfterLastDataInA = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1

    --
    Rick (MVP - Excel)


    "Miree" <> wrote in message
    news:...
    > sorry this didnt work, what i think might be eaisier(for my simple midnd)
    > is
    > if you could help me with a code to identify and select the row one down
    > from
    > the last data point in column A
    >
    > "Jacob Skaria" wrote:
    >
    >> Dim lngTemp, lngRow
    >>
    >> lngRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    >>
    >> For lngTemp = lngRow To 1 Step -1
    >> If InStr(Range("DT" & lngTemp), UCase(UserForm7.TextBox2.Text)) = 0
    >> Then
    >> Activesheet.Cells(lngTemp).EntireRow.Delete
    >> End If
    >> Next lngTemp
    >>
    >> If this post helps click Yes
    >> ---------------
    >> Jacob Skaria
    >>
    >>
    >> "Miree" wrote:
    >>
    >> > I need to modify my code to search from the last row, at the moment it
    >> > only
    >> > goes from the last row there is data in for my specific column(DT), can
    >> > I
    >> > make it start from the last row where there is data from column A but
    >> > only
    >> > searching the column i need(DT)
    >> >
    >> > Dim rng As Range
    >> > Dim i As Long
    >> >
    >> > Set rng = ActiveSheet.Range(Cells(1, "DT"), Cells(Rows.Count,
    >> > "DT").End(xlUp))
    >> >
    >> > With rng
    >> > For i = .Rows.Count To 1 Step -1
    >> > If InStr(UCase(.Cells(i)), UCase(UserForm7.TextBox2.Text)) = 0
    >> > Then
    >> > .Cells(i).EntireRow.Delete
    >> > End If
    >> > Next i
    >> > End With
    >> >
    >> > Thank you in advance
     
    Rick Rothstein, Apr 2, 2009
    #4
  5. Miree

    Miree Guest

    Im sorry all this coding is new to me, I am trying to learn as I go, and
    because of this I dont understand some basics. Most of what I get is copied
    and paste from else where, i dont understand what it does but know it does
    what i need it to.

    What I am trying to do is select the last row + 1 and then apply this to it
    Cells.Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = ".."

    although this line finds the row i need, how can i get it to select it?

    RowAfterLastDataInA = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1

    "Rick Rothstein" wrote:

    > Jacob gave you that (well, except for the "one down from" part). Jacob
    > posted this...
    >
    > lngRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    >
    > which is the row containing the last piece of data in Column A. Since you
    > want one down from this, just add one to it...
    >
    > RowAfterLastDataInA = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
    >
    > --
    > Rick (MVP - Excel)
    >
    >
    > "Miree" <> wrote in message
    > news:...
    > > sorry this didnt work, what i think might be eaisier(for my simple midnd)
    > > is
    > > if you could help me with a code to identify and select the row one down
    > > from
    > > the last data point in column A
    > >
    > > "Jacob Skaria" wrote:
    > >
    > >> Dim lngTemp, lngRow
    > >>
    > >> lngRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    > >>
    > >> For lngTemp = lngRow To 1 Step -1
    > >> If InStr(Range("DT" & lngTemp), UCase(UserForm7.TextBox2.Text)) = 0
    > >> Then
    > >> Activesheet.Cells(lngTemp).EntireRow.Delete
    > >> End If
    > >> Next lngTemp
    > >>
    > >> If this post helps click Yes
    > >> ---------------
    > >> Jacob Skaria
    > >>
    > >>
    > >> "Miree" wrote:
    > >>
    > >> > I need to modify my code to search from the last row, at the moment it
    > >> > only
    > >> > goes from the last row there is data in for my specific column(DT), can
    > >> > I
    > >> > make it start from the last row where there is data from column A but
    > >> > only
    > >> > searching the column i need(DT)
    > >> >
    > >> > Dim rng As Range
    > >> > Dim i As Long
    > >> >
    > >> > Set rng = ActiveSheet.Range(Cells(1, "DT"), Cells(Rows.Count,
    > >> > "DT").End(xlUp))
    > >> >
    > >> > With rng
    > >> > For i = .Rows.Count To 1 Step -1
    > >> > If InStr(UCase(.Cells(i)), UCase(UserForm7.TextBox2.Text)) = 0
    > >> > Then
    > >> > .Cells(i).EntireRow.Delete
    > >> > End If
    > >> > Next i
    > >> > End With
    > >> >
    > >> > Thank you in advance

    >
    >
     
    Miree, Apr 2, 2009
    #5
  6. Miree

    Jacob Skaria Guest

    Will give you an example
    If I want to select Range(A1:A12) mention that as

    Range("A1:A" & variable).Select


    If this post helps click Yes
    ---------------
    Jacob Skaria


    "Miree" wrote:

    > Im sorry all this coding is new to me, I am trying to learn as I go, and
    > because of this I dont understand some basics. Most of what I get is copied
    > and paste from else where, i dont understand what it does but know it does
    > what i need it to.
    >
    > What I am trying to do is select the last row + 1 and then apply this to it
    > Cells.Select
    > Selection.SpecialCells(xlCellTypeBlanks).Select
    > Selection.FormulaR1C1 = ".."
    >
    > although this line finds the row i need, how can i get it to select it?
    >
    > RowAfterLastDataInA = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
    >
    > "Rick Rothstein" wrote:
    >
    > > Jacob gave you that (well, except for the "one down from" part). Jacob
    > > posted this...
    > >
    > > lngRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    > >
    > > which is the row containing the last piece of data in Column A. Since you
    > > want one down from this, just add one to it...
    > >
    > > RowAfterLastDataInA = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
    > >
    > > --
    > > Rick (MVP - Excel)
    > >
    > >
    > > "Miree" <> wrote in message
    > > news:...
    > > > sorry this didnt work, what i think might be eaisier(for my simple midnd)
    > > > is
    > > > if you could help me with a code to identify and select the row one down
    > > > from
    > > > the last data point in column A
    > > >
    > > > "Jacob Skaria" wrote:
    > > >
    > > >> Dim lngTemp, lngRow
    > > >>
    > > >> lngRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    > > >>
    > > >> For lngTemp = lngRow To 1 Step -1
    > > >> If InStr(Range("DT" & lngTemp), UCase(UserForm7.TextBox2.Text)) = 0
    > > >> Then
    > > >> Activesheet.Cells(lngTemp).EntireRow.Delete
    > > >> End If
    > > >> Next lngTemp
    > > >>
    > > >> If this post helps click Yes
    > > >> ---------------
    > > >> Jacob Skaria
    > > >>
    > > >>
    > > >> "Miree" wrote:
    > > >>
    > > >> > I need to modify my code to search from the last row, at the moment it
    > > >> > only
    > > >> > goes from the last row there is data in for my specific column(DT), can
    > > >> > I
    > > >> > make it start from the last row where there is data from column A but
    > > >> > only
    > > >> > searching the column i need(DT)
    > > >> >
    > > >> > Dim rng As Range
    > > >> > Dim i As Long
    > > >> >
    > > >> > Set rng = ActiveSheet.Range(Cells(1, "DT"), Cells(Rows.Count,
    > > >> > "DT").End(xlUp))
    > > >> >
    > > >> > With rng
    > > >> > For i = .Rows.Count To 1 Step -1
    > > >> > If InStr(UCase(.Cells(i)), UCase(UserForm7.TextBox2.Text)) = 0
    > > >> > Then
    > > >> > .Cells(i).EntireRow.Delete
    > > >> > End If
    > > >> > Next i
    > > >> > End With
    > > >> >
    > > >> > Thank you in advance

    > >
    > >
     
    Jacob Skaria, Apr 2, 2009
    #6
  7. Miree

    Jacob Skaria Guest

    Thanks Rick

    Dear Miree..Sorry, you have not mentioned about Selection.FormulaR1C1 = ".."
    in your initial post. The code which we have posted will satisfy the
    requirement mentioned "Search up from last row"..


    If this post helps click Yes
    ---------------
    Jacob Skaria


    "Miree" wrote:

    > Im sorry all this coding is new to me, I am trying to learn as I go, and
    > because of this I dont understand some basics. Most of what I get is copied
    > and paste from else where, i dont understand what it does but know it does
    > what i need it to.
    >
    > What I am trying to do is select the last row + 1 and then apply this to it
    > Cells.Select
    > Selection.SpecialCells(xlCellTypeBlanks).Select
    > Selection.FormulaR1C1 = ".."
    >
    > although this line finds the row i need, how can i get it to select it?
    >
    > RowAfterLastDataInA = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
    >
    > "Rick Rothstein" wrote:
    >
    > > Jacob gave you that (well, except for the "one down from" part). Jacob
    > > posted this...
    > >
    > > lngRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    > >
    > > which is the row containing the last piece of data in Column A. Since you
    > > want one down from this, just add one to it...
    > >
    > > RowAfterLastDataInA = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
    > >
    > > --
    > > Rick (MVP - Excel)
    > >
    > >
    > > "Miree" <> wrote in message
    > > news:...
    > > > sorry this didnt work, what i think might be eaisier(for my simple midnd)
    > > > is
    > > > if you could help me with a code to identify and select the row one down
    > > > from
    > > > the last data point in column A
    > > >
    > > > "Jacob Skaria" wrote:
    > > >
    > > >> Dim lngTemp, lngRow
    > > >>
    > > >> lngRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    > > >>
    > > >> For lngTemp = lngRow To 1 Step -1
    > > >> If InStr(Range("DT" & lngTemp), UCase(UserForm7.TextBox2.Text)) = 0
    > > >> Then
    > > >> Activesheet.Cells(lngTemp).EntireRow.Delete
    > > >> End If
    > > >> Next lngTemp
    > > >>
    > > >> If this post helps click Yes
    > > >> ---------------
    > > >> Jacob Skaria
    > > >>
    > > >>
    > > >> "Miree" wrote:
    > > >>
    > > >> > I need to modify my code to search from the last row, at the moment it
    > > >> > only
    > > >> > goes from the last row there is data in for my specific column(DT), can
    > > >> > I
    > > >> > make it start from the last row where there is data from column A but
    > > >> > only
    > > >> > searching the column i need(DT)
    > > >> >
    > > >> > Dim rng As Range
    > > >> > Dim i As Long
    > > >> >
    > > >> > Set rng = ActiveSheet.Range(Cells(1, "DT"), Cells(Rows.Count,
    > > >> > "DT").End(xlUp))
    > > >> >
    > > >> > With rng
    > > >> > For i = .Rows.Count To 1 Step -1
    > > >> > If InStr(UCase(.Cells(i)), UCase(UserForm7.TextBox2.Text)) = 0
    > > >> > Then
    > > >> > .Cells(i).EntireRow.Delete
    > > >> > End If
    > > >> > Next i
    > > >> > End With
    > > >> >
    > > >> > Thank you in advance

    > >
    > >
     
    Jacob Skaria, Apr 2, 2009
    #7
  8. Miree

    Miree Guest

    I understand that but what i dont understand it is if i dont know what range
    i want to select how do i tell it to select the last row(in A) +1

    "Jacob Skaria" wrote:

    > Will give you an example
    > If I want to select Range(A1:A12) mention that as
    >
    > Range("A1:A" & variable).Select
    >
    >
    > If this post helps click Yes
    > ---------------
    > Jacob Skaria
    >
    >
    > "Miree" wrote:
    >
    > > Im sorry all this coding is new to me, I am trying to learn as I go, and
    > > because of this I dont understand some basics. Most of what I get is copied
    > > and paste from else where, i dont understand what it does but know it does
    > > what i need it to.
    > >
    > > What I am trying to do is select the last row + 1 and then apply this to it
    > > Cells.Select
    > > Selection.SpecialCells(xlCellTypeBlanks).Select
    > > Selection.FormulaR1C1 = ".."
    > >
    > > although this line finds the row i need, how can i get it to select it?
    > >
    > > RowAfterLastDataInA = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
    > >
    > > "Rick Rothstein" wrote:
    > >
    > > > Jacob gave you that (well, except for the "one down from" part). Jacob
    > > > posted this...
    > > >
    > > > lngRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    > > >
    > > > which is the row containing the last piece of data in Column A. Since you
    > > > want one down from this, just add one to it...
    > > >
    > > > RowAfterLastDataInA = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
    > > >
    > > > --
    > > > Rick (MVP - Excel)
    > > >
    > > >
    > > > "Miree" <> wrote in message
    > > > news:...
    > > > > sorry this didnt work, what i think might be eaisier(for my simple midnd)
    > > > > is
    > > > > if you could help me with a code to identify and select the row one down
    > > > > from
    > > > > the last data point in column A
    > > > >
    > > > > "Jacob Skaria" wrote:
    > > > >
    > > > >> Dim lngTemp, lngRow
    > > > >>
    > > > >> lngRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    > > > >>
    > > > >> For lngTemp = lngRow To 1 Step -1
    > > > >> If InStr(Range("DT" & lngTemp), UCase(UserForm7.TextBox2.Text)) = 0
    > > > >> Then
    > > > >> Activesheet.Cells(lngTemp).EntireRow.Delete
    > > > >> End If
    > > > >> Next lngTemp
    > > > >>
    > > > >> If this post helps click Yes
    > > > >> ---------------
    > > > >> Jacob Skaria
    > > > >>
    > > > >>
    > > > >> "Miree" wrote:
    > > > >>
    > > > >> > I need to modify my code to search from the last row, at the moment it
    > > > >> > only
    > > > >> > goes from the last row there is data in for my specific column(DT), can
    > > > >> > I
    > > > >> > make it start from the last row where there is data from column A but
    > > > >> > only
    > > > >> > searching the column i need(DT)
    > > > >> >
    > > > >> > Dim rng As Range
    > > > >> > Dim i As Long
    > > > >> >
    > > > >> > Set rng = ActiveSheet.Range(Cells(1, "DT"), Cells(Rows.Count,
    > > > >> > "DT").End(xlUp))
    > > > >> >
    > > > >> > With rng
    > > > >> > For i = .Rows.Count To 1 Step -1
    > > > >> > If InStr(UCase(.Cells(i)), UCase(UserForm7.TextBox2.Text)) = 0
    > > > >> > Then
    > > > >> > .Cells(i).EntireRow.Delete
    > > > >> > End If
    > > > >> > Next i
    > > > >> > End With
    > > > >> >
    > > > >> > Thank you in advance
    > > >
    > > >
     
    Miree, Apr 2, 2009
    #8
  9. Miree

    Jacob Skaria Guest

    Please try this

    lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    Range("A1:A" & lngLastRow +1).Select

    If this post helps click Yes
    ---------------
    Jacob Skaria


    "Miree" wrote:

    > I understand that but what i dont understand it is if i dont know what range
    > i want to select how do i tell it to select the last row(in A) +1
    >
    > "Jacob Skaria" wrote:
    >
    > > Will give you an example
    > > If I want to select Range(A1:A12) mention that as
    > >
    > > Range("A1:A" & variable).Select
    > >
    > >
    > > If this post helps click Yes
    > > ---------------
    > > Jacob Skaria
    > >
    > >
    > > "Miree" wrote:
    > >
    > > > Im sorry all this coding is new to me, I am trying to learn as I go, and
    > > > because of this I dont understand some basics. Most of what I get is copied
    > > > and paste from else where, i dont understand what it does but know it does
    > > > what i need it to.
    > > >
    > > > What I am trying to do is select the last row + 1 and then apply this to it
    > > > Cells.Select
    > > > Selection.SpecialCells(xlCellTypeBlanks).Select
    > > > Selection.FormulaR1C1 = ".."
    > > >
    > > > although this line finds the row i need, how can i get it to select it?
    > > >
    > > > RowAfterLastDataInA = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
    > > >
    > > > "Rick Rothstein" wrote:
    > > >
    > > > > Jacob gave you that (well, except for the "one down from" part). Jacob
    > > > > posted this...
    > > > >
    > > > > lngRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    > > > >
    > > > > which is the row containing the last piece of data in Column A. Since you
    > > > > want one down from this, just add one to it...
    > > > >
    > > > > RowAfterLastDataInA = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
    > > > >
    > > > > --
    > > > > Rick (MVP - Excel)
    > > > >
    > > > >
    > > > > "Miree" <> wrote in message
    > > > > news:...
    > > > > > sorry this didnt work, what i think might be eaisier(for my simple midnd)
    > > > > > is
    > > > > > if you could help me with a code to identify and select the row one down
    > > > > > from
    > > > > > the last data point in column A
    > > > > >
    > > > > > "Jacob Skaria" wrote:
    > > > > >
    > > > > >> Dim lngTemp, lngRow
    > > > > >>
    > > > > >> lngRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    > > > > >>
    > > > > >> For lngTemp = lngRow To 1 Step -1
    > > > > >> If InStr(Range("DT" & lngTemp), UCase(UserForm7.TextBox2.Text)) = 0
    > > > > >> Then
    > > > > >> Activesheet.Cells(lngTemp).EntireRow.Delete
    > > > > >> End If
    > > > > >> Next lngTemp
    > > > > >>
    > > > > >> If this post helps click Yes
    > > > > >> ---------------
    > > > > >> Jacob Skaria
    > > > > >>
    > > > > >>
    > > > > >> "Miree" wrote:
    > > > > >>
    > > > > >> > I need to modify my code to search from the last row, at the moment it
    > > > > >> > only
    > > > > >> > goes from the last row there is data in for my specific column(DT), can
    > > > > >> > I
    > > > > >> > make it start from the last row where there is data from column A but
    > > > > >> > only
    > > > > >> > searching the column i need(DT)
    > > > > >> >
    > > > > >> > Dim rng As Range
    > > > > >> > Dim i As Long
    > > > > >> >
    > > > > >> > Set rng = ActiveSheet.Range(Cells(1, "DT"), Cells(Rows.Count,
    > > > > >> > "DT").End(xlUp))
    > > > > >> >
    > > > > >> > With rng
    > > > > >> > For i = .Rows.Count To 1 Step -1
    > > > > >> > If InStr(UCase(.Cells(i)), UCase(UserForm7.TextBox2.Text)) = 0
    > > > > >> > Then
    > > > > >> > .Cells(i).EntireRow.Delete
    > > > > >> > End If
    > > > > >> > Next i
    > > > > >> > End With
    > > > > >> >
    > > > > >> > Thank you in advance
    > > > >
    > > > >
     
    Jacob Skaria, Apr 2, 2009
    #9
  10. Miree

    Miree Guest

    had to modify slightly to just do last row, but works perfectly now, thank
    you very super much to both of you.

    "Jacob Skaria" wrote:

    > Thanks Rick
    >
    > Dear Miree..Sorry, you have not mentioned about Selection.FormulaR1C1 = ".."
    > in your initial post. The code which we have posted will satisfy the
    > requirement mentioned "Search up from last row"..
    >
    >
    > If this post helps click Yes
    > ---------------
    > Jacob Skaria
    >
    >
    > "Miree" wrote:
    >
    > > Im sorry all this coding is new to me, I am trying to learn as I go, and
    > > because of this I dont understand some basics. Most of what I get is copied
    > > and paste from else where, i dont understand what it does but know it does
    > > what i need it to.
    > >
    > > What I am trying to do is select the last row + 1 and then apply this to it
    > > Cells.Select
    > > Selection.SpecialCells(xlCellTypeBlanks).Select
    > > Selection.FormulaR1C1 = ".."
    > >
    > > although this line finds the row i need, how can i get it to select it?
    > >
    > > RowAfterLastDataInA = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
    > >
    > > "Rick Rothstein" wrote:
    > >
    > > > Jacob gave you that (well, except for the "one down from" part). Jacob
    > > > posted this...
    > > >
    > > > lngRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    > > >
    > > > which is the row containing the last piece of data in Column A. Since you
    > > > want one down from this, just add one to it...
    > > >
    > > > RowAfterLastDataInA = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
    > > >
    > > > --
    > > > Rick (MVP - Excel)
    > > >
    > > >
    > > > "Miree" <> wrote in message
    > > > news:...
    > > > > sorry this didnt work, what i think might be eaisier(for my simple midnd)
    > > > > is
    > > > > if you could help me with a code to identify and select the row one down
    > > > > from
    > > > > the last data point in column A
    > > > >
    > > > > "Jacob Skaria" wrote:
    > > > >
    > > > >> Dim lngTemp, lngRow
    > > > >>
    > > > >> lngRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    > > > >>
    > > > >> For lngTemp = lngRow To 1 Step -1
    > > > >> If InStr(Range("DT" & lngTemp), UCase(UserForm7.TextBox2.Text)) = 0
    > > > >> Then
    > > > >> Activesheet.Cells(lngTemp).EntireRow.Delete
    > > > >> End If
    > > > >> Next lngTemp
    > > > >>
    > > > >> If this post helps click Yes
    > > > >> ---------------
    > > > >> Jacob Skaria
    > > > >>
    > > > >>
    > > > >> "Miree" wrote:
    > > > >>
    > > > >> > I need to modify my code to search from the last row, at the moment it
    > > > >> > only
    > > > >> > goes from the last row there is data in for my specific column(DT), can
    > > > >> > I
    > > > >> > make it start from the last row where there is data from column A but
    > > > >> > only
    > > > >> > searching the column i need(DT)
    > > > >> >
    > > > >> > Dim rng As Range
    > > > >> > Dim i As Long
    > > > >> >
    > > > >> > Set rng = ActiveSheet.Range(Cells(1, "DT"), Cells(Rows.Count,
    > > > >> > "DT").End(xlUp))
    > > > >> >
    > > > >> > With rng
    > > > >> > For i = .Rows.Count To 1 Step -1
    > > > >> > If InStr(UCase(.Cells(i)), UCase(UserForm7.TextBox2.Text)) = 0
    > > > >> > Then
    > > > >> > .Cells(i).EntireRow.Delete
    > > > >> > End If
    > > > >> > Next i
    > > > >> > End With
    > > > >> >
    > > > >> > Thank you in advance
    > > >
    > > >
     
    Miree, Apr 2, 2009
    #10
  11. Miree

    Jacob Skaria Guest

    Not sure whether you have seen this post

    lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    Range("A1:A" & lngLastRow +1).Select

    If this post helps click Yes
    ---------------
    Jacob Skaria

    "Miree" wrote:

    > had to modify slightly to just do last row, but works perfectly now, thank
    > you very super much to both of you.
    >
    > "Jacob Skaria" wrote:
    >
    > > Thanks Rick
    > >
    > > Dear Miree..Sorry, you have not mentioned about Selection.FormulaR1C1 = ".."
    > > in your initial post. The code which we have posted will satisfy the
    > > requirement mentioned "Search up from last row"..
    > >
    > >
    > > If this post helps click Yes
    > > ---------------
    > > Jacob Skaria
    > >
    > >
    > > "Miree" wrote:
    > >
    > > > Im sorry all this coding is new to me, I am trying to learn as I go, and
    > > > because of this I dont understand some basics. Most of what I get is copied
    > > > and paste from else where, i dont understand what it does but know it does
    > > > what i need it to.
    > > >
    > > > What I am trying to do is select the last row + 1 and then apply this to it
    > > > Cells.Select
    > > > Selection.SpecialCells(xlCellTypeBlanks).Select
    > > > Selection.FormulaR1C1 = ".."
    > > >
    > > > although this line finds the row i need, how can i get it to select it?
    > > >
    > > > RowAfterLastDataInA = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
    > > >
    > > > "Rick Rothstein" wrote:
    > > >
    > > > > Jacob gave you that (well, except for the "one down from" part). Jacob
    > > > > posted this...
    > > > >
    > > > > lngRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    > > > >
    > > > > which is the row containing the last piece of data in Column A. Since you
    > > > > want one down from this, just add one to it...
    > > > >
    > > > > RowAfterLastDataInA = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
    > > > >
    > > > > --
    > > > > Rick (MVP - Excel)
    > > > >
    > > > >
    > > > > "Miree" <> wrote in message
    > > > > news:...
    > > > > > sorry this didnt work, what i think might be eaisier(for my simple midnd)
    > > > > > is
    > > > > > if you could help me with a code to identify and select the row one down
    > > > > > from
    > > > > > the last data point in column A
    > > > > >
    > > > > > "Jacob Skaria" wrote:
    > > > > >
    > > > > >> Dim lngTemp, lngRow
    > > > > >>
    > > > > >> lngRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    > > > > >>
    > > > > >> For lngTemp = lngRow To 1 Step -1
    > > > > >> If InStr(Range("DT" & lngTemp), UCase(UserForm7.TextBox2.Text)) = 0
    > > > > >> Then
    > > > > >> Activesheet.Cells(lngTemp).EntireRow.Delete
    > > > > >> End If
    > > > > >> Next lngTemp
    > > > > >>
    > > > > >> If this post helps click Yes
    > > > > >> ---------------
    > > > > >> Jacob Skaria
    > > > > >>
    > > > > >>
    > > > > >> "Miree" wrote:
    > > > > >>
    > > > > >> > I need to modify my code to search from the last row, at the moment it
    > > > > >> > only
    > > > > >> > goes from the last row there is data in for my specific column(DT), can
    > > > > >> > I
    > > > > >> > make it start from the last row where there is data from column A but
    > > > > >> > only
    > > > > >> > searching the column i need(DT)
    > > > > >> >
    > > > > >> > Dim rng As Range
    > > > > >> > Dim i As Long
    > > > > >> >
    > > > > >> > Set rng = ActiveSheet.Range(Cells(1, "DT"), Cells(Rows.Count,
    > > > > >> > "DT").End(xlUp))
    > > > > >> >
    > > > > >> > With rng
    > > > > >> > For i = .Rows.Count To 1 Step -1
    > > > > >> > If InStr(UCase(.Cells(i)), UCase(UserForm7.TextBox2.Text)) = 0
    > > > > >> > Then
    > > > > >> > .Cells(i).EntireRow.Delete
    > > > > >> > End If
    > > > > >> > Next i
    > > > > >> > End With
    > > > > >> >
    > > > > >> > Thank you in advance
    > > > >
    > > > >
     
    Jacob Skaria, Apr 2, 2009
    #11
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. VB Script for Excel
    Replies:
    2
    Views:
    273
    Guest
    May 25, 2006
  2. Guest

    Fill down to last row - copy formula to last row

    Guest, Oct 6, 2006, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    702
    Guest
    Oct 6, 2006
  3. Guest

    The best sub for last row / last column / last cell?

    Guest, Jan 28, 2007, in forum: Microsoft Excel Programming
    Replies:
    15
    Views:
    720
    Guest
    Feb 2, 2007
  4. Guest

    Copy last row from sheet 1 to last row in sheet 2

    Guest, Jul 7, 2007, in forum: Microsoft Excel Programming
    Replies:
    4
    Views:
    270
    Dave Peterson
    Jul 8, 2007
  5. soccerdav2003

    Macro to find last row with Data, avg last 30, show result on row

    soccerdav2003, Jun 11, 2008, in forum: Microsoft Excel Programming
    Replies:
    7
    Views:
    303
Loading...

Share This Page