Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

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

  1. #1
    Join Date
    18th October 2010
    Location
    Sydney, Australia
    Posts
    62

    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

    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    17,444

    Re: "<" 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

    VB:
    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

    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

    About me.

  3. #3
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    17,444

    Re: "<" 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.

    VB:
    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

    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

    About me.

  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,698

    Re: "<" operator not working in simple IF statement

    Try
    VB:
    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 
    
    

  5. #5
    Join Date
    18th October 2010
    Location
    Sydney, Australia
    Posts
    62

    Re: "<" 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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    17,444

    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

    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

    About me.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 3
    Last Post: April 27th, 2012, 21:54
  2. Allow User To Color Cell In VBA Macro Code
    By london_uk in forum EXCEL HELP
    Replies: 10
    Last Post: July 6th, 2010, 20:10
  3. Compare Columns For Name Collected From User
    By welovevba in forum EXCEL HELP
    Replies: 4
    Last Post: April 25th, 2009, 06:52
  4. Replies: 1
    Last Post: April 22nd, 2009, 02:55
  5. Replies: 6
    Last Post: February 14th, 2009, 05:35

Bookmarks

Posting Permissions

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