Announcement

Collapse
No announcement yet.

VBA Macro Code To Delete Row If Value Collected From User Is Less Than Cell Value

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA Macro Code To Delete Row If Value Collected From User Is Less Than Cell Value



    Hi all excel fans

    I have a very simple code which is to delete the row based upon an input box entry. If the value of cell A1 is less than the input box entry, then row 1 is to be deleted. If the value of cell A1 is greater than the input box entry, then no row should be deleted.

    It seems like a pretty straight forward request, but it doesn't matter what I enter into the input box - it deletes row 1 and my IF statement using the less than operator does not appear to work. Very frustrated.....please see my code below. Am I missing some basic concept?

    Thanks
    Russel

    Code:
    Sub deleterow()
    
    
    Amount = InputBox("Enter Value")
    Sheets("TestS").Activate
    ActiveSheet.Range("A1").Select
     
    CellValue = ActiveSheet.Range("A1").Value
    
    
    If CellValue < Amount Then ActiveCell.EntireRow.Delete
    
    
    End Sub

  • #2
    Re: &quot;&lt;&quot; operator not working in simple IF statement

    You don't declare any variables, which is bad practice. You don't need to select the sheet or range

    Option Explicit

    Sub deleterow()
    Dim Amount As Double
    On Error GoTo exit_proc
    Amount = CDbl(InputBox("Enter Value"))

    With Sheets("TestS").Range("A1")
    If .Value < Amount Then .EntireRow.Delete
    End With
    exit_proc:
    End Sub
    Hope that Helps

    Roy

    New users should read the Forum Rules before posting

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

    Comment


    • #3
      Re: &quot;&lt;&quot; operator not working in simple IF statement

      You don't declare any variables, which is bad practice. You don't need to select the sheet or range, presumably you are using numbers.

      Option Explicit

      Sub deleterow()
      Dim Amount As Double
      On Error GoTo exit_proc
      Amount = CDbl(InputBox("Enter Value"))

      With Sheets("TestS").Range("A1")
      If .Value < Amount Then .EntireRow.Delete
      End With
      exit_proc:
      End Sub
      Hope that Helps

      Roy

      New users should read the Forum Rules before posting

      For free Excel tools & articles visit my web site

      If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

      RoyUK's Web Site

      royUK's Database Form

      Where to paste code from the Forum

      About me.

      Comment


      • #4
        Re: &quot;&lt;&quot; operator not working in simple IF statement

        Try
        Code:
        Sub deleterow()
        Dim CellValue As Double, Amount As Double
             
                Amount = Application.InputBox(Prompt:="Enter Value", Type:=1)
                If Amount = 0 Then Exit Sub
                CellValue = Sheets("TestS").Range("A1")
                If CellValue < Amount Then Sheets("TestS").Range("A1").EntireRow.Delete
        End Sub

        Comment


        • #5
          Re: &quot;&lt;&quot; operator not working in simple IF statement

          Hi Roy and Dave

          Thank you both for your answers.

          Whilst my original code did declare variables (tried long and integer), I think it was ultimately this as my downfall. Declaring the variable as Double worked by itself, as did Amount = CDbl(...............). Both of these changes worked independently.

          Not quite sure what this does, but it's obviously very important. I thought declaring the value as Long or Integer would have been good enough, but apparently not.

          Learning all the time!

          Thanks heaps.
          Russel

          Comment


          • #6


            Re: VBA Macro Code To Delete Row If Value Collected From User Is Less Than Cell Value

            Double is similar to Long, etc. Very much like Currency as it allows decimal input.

            You could hav used Val instead of CDbl to convert to a numeric value.
            Hope that Helps

            Roy

            New users should read the Forum Rules before posting

            For free Excel tools & articles visit my web site

            If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

            RoyUK's Web Site

            royUK's Database Form

            Where to paste code from the Forum

            About me.

            Comment

            Working...
            X