Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / 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

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    19,293

    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

    [vba]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
    [/vba]
    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

    Where to paste code from the Forum

    About me.

  3. #3
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    19,293

    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.

    [vba]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
    [/vba]
    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

    Where to paste code from the Forum

    About me.

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

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

  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
    19,293

    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

    Where to paste code from the Forum

    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, 22: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, 21:10
  3. Compare Columns For Name Collected From User
    By welovevba in forum EXCEL HELP
    Replies: 4
    Last Post: April 25th, 2009, 07:52
  4. Replies: 1
    Last Post: April 22nd, 2009, 03:55
  5. Replies: 6
    Last Post: February 14th, 2009, 06: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