Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Need help with these loops (Excel xp)

    I am working on this macro to look for values that are less than a value the user inputs and then deletes the row. This is what i've done so far...

    Sub DeleteRowsValues()
    Dim lLastRow As Long, I As Long
    Dim LookRow As Range
    Dim LookColumn As Range
    Dim rngCell As Range
    Dim rngTC As Range

    lLastRow = ActiveSheet.Range("A65536").End(xlUp).Row
    LookForValue = InputBox("Delete rows that have a value of less than:")
    LookInTheseColumns = InputBox("from Column B to Column:")

    Range("B1").Select
    Set LookColumn = Range("B1:" & LookInTheseColumns & lLastRow)

    For I = 1 To lLastRow
    Set LookRow = Range("A" & I & ":" & LookInTheseColumns & I)
    For Each rngCell In LookRow 'look in the row
    If rngCell.Value < LookForValue Then
    rngCell.Offset(0, 1).Select
    End If
    Next rngCell
    rngCell.EntireRow.Delete
    Next I

    End Sub



    I just don't know how to delete the row when all the values from B to whatever column the user inputs is TRUE.

    I don't think i have the proper Set statements and the proper order going here. Can i please get help with the proper logic. Thank you

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need help with these loops (Excel xp)

    It is not clear from your message when you want to delete the rows. I am guessing that you want to delete the row if ALL of the values in the columns between B and the column entered are less than the value entered, and not delete the row if ANY value in those columns is greater than or equal to the value entered. If that is correct, the following should do what you want:

    <pre>Public Sub DeleteRowsValues()
    Dim lLastRow As Long, I As Long, J As Long, lLastCol As Long
    Dim dLookForValue As Double, strLookInTheseColumns As String
    Dim bAllLessThan As Boolean
    lLastRow = ActiveSheet.Range("A65535").End(xlUp).Row - 1
    dLookForValue = InputBox("Delete rows that have a value of less than:")
    strLookInTheseColumns = UCase(InputBox("from Column B to Column:"))
    lLastCol = ActiveSheet.Range("B1:" & strLookInTheseColumns & "1").Columns.Count - 1
    For I = lLastRow To 0 Step -1
    bAllLessThan = True
    For J = 0 To lLastCol
    If Not (ActiveSheet.Range("B1").Offset(I, J).Value < dLookForValue) Then
    bAllLessThan = False
    Exit For
    End If
    Next J
    If bAllLessThan Then
    ActiveSheet.Range("B1").Offset(I, J).EntireRow.Delete
    End If
    Next I
    End Sub
    </pre>

    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •