Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Delete rows based on criteria from input box

  1. #1
    Join Date
    23rd November 2010
    Posts
    3

    Delete rows based on criteria from input box

    Hello

    I have no prior experience with VBA but I need to implement a Macro that is relatively simple I think.

    I found a script that suits my purpose very well but it needs a minor change. The script will search for a value in a defined colum and if found delete the entire row.

    The problem is that I need to select the entire colum (first to last value) in order define where the Macro should search. BUT if I select the column by just clicking column letter (lets say B) and then run the Macro I will get an overflow error.

    Is it possible to change the Macro in a way so that it just ignores the empty fields so that I can just quickly click on the column letter and then start the Macro?

    Here is the script that I found and would like to change.

    Please be aware, I have no VBA experience so in order to help me you have to write the lines that need to be added.

    Ahh, on thing, maybe it is even possible to include the column letter directly into the Macro script as it stays always the same. In my case that would be the letter 'G'. The value of the that needs to be searched for will change just like in the script below.

    Thank you very much for helping me out.

    Greetings
    Toni

    VB:
    Sub DeleteRows() 
         
        Dim strToDelete As String 
        Dim rngSrc As Range 
        Dim NumRows As Integer 
        Dim ThisRow As Integer 
        Dim ThatRow As Integer 
        Dim ThisCol As Integer 
        Dim J As Integer 
        Dim DeletedRows As Integer 
         
        strToDelete = InputBox("Value to Trigger Delete?", "Delete Rows") 
        Set rngSrc = ActiveSheet.Range(ActiveWindow.Selection.Address) 
         
        NumRows = rngSrc.Rows.Count ThisRow = rngSrc.Row 
        ThatRow = ThisRow + NumRows - 1 ThisCol = rngSrc.Column 
         
        For J = ThatRow To ThisRow Step -1 If Cells(J, ThisCol) = strToDelete Then Rows(J).Select 
            Selection.Delete Shift:=xlUp DeletedRows = DeletedRows + 1 
             
        End If 
    Next J MsgBox "Number of deleted rows: " & DeletedRows 
     
    End Sub 
    
    
    Last edited by AAE; November 25th, 2010 at 22:48. Reason: revise thread title, add code tags

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    6th September 2010
    Location
    Madurai, India
    Posts
    144

    Re: VBA - Delete rows - small issue

    Hi Tony,

    Please use [CODE] tags next time when you include your code in this forum.

    You can simply use the in-built excel function "Find" in the code.

    This would be as below:
    VB:
    Dim strTodel As String 
    Dim colTofind As String 
    Dim delRows As Long 
    strTodel=InputBox("Enter the String") 
    Do While True 
        On Error Goto Err_handle 
        Range("B:B").Find(What:=strTodel,LookIn:=xlFormulas,LookAt:=xlWhole,After:=Activecell).EntireRow.Delete Shift:=xlUp 
        delRows=delRows + 1 
    Loop 
    Err_handle: 
    MsgBox "Number of Delete Rows - " & delRows 
    Exit Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    23rd November 2010
    Posts
    3

    Re: VBA - Delete rows - small issue

    @ shreenice

    This works like a charm! :-) Thank you very much indeed!!!

    Greetings
    Toni

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: Delete rows based on criteria from input box

    Hello toniri,

    Welcome to Ozgrid.

    Because thread titles are used in searching the forum it is vital they be written using ONLY search friendly key words that accurately describe your thread content or overall objective. Titles must not use non-essential verbiage like: "small issue", or others such as: "urgent", "noob","I need", trying to", etc. Such words dilute the title and, consequently, the search results.

    Please note the change to your title as an example and in future threads take care to write a proper title that precisely defines your thread. Thanks.

    Also, per the forum rules you are required to use code tags for all VBA code. I've added the tags for your this time only. Be sure to use them in future posts.

    How to use code tags

    [code]
    your code here
    [/code]
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  5. #5
    Join Date
    23rd November 2010
    Posts
    3

    Re: Delete rows based on criteria from input box

    Quote Originally Posted by AAE View Post
    Hello toniri,

    Welcome to Ozgrid.

    Because thread titles are used in searching the forum it is vital they be written using ONLY search friendly key words that accurately describe your thread content or overall objective. Titles must not use non-essential verbiage like: "small issue", or others such as: "urgent", "noob","I need", trying to", etc. Such words dilute the title and, consequently, the search results.

    Please note the change to your title as an example and in future threads take care to write a proper title that precisely defines your thread. Thanks.

    Also, per the forum rules you are required to use code tags for all VBA code. I've added the tags for your this time only. Be sure to use them in future posts.

    How to use code tags

    [code]
    your code here
    [/code]
    Hello AAE

    Thank you for your message. I have to admit that I had not read the board rules when I posted my question. Actually, I was kind of unhappy, how my code snippet was displayed and edited the post to try and make it more readable. Your advice reagarding the naming of the title makes a lot of sense too, I will follow your guidelines.

    Greetings
    Tonir.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. issue hiding rows using VBA
    By bogeyman in forum EXCEL HELP
    Replies: 3
    Last Post: February 7th, 2006, 00:54
  2. Replies: 8
    Last Post: August 24th, 2005, 22:15
  3. Replies: 2
    Last Post: July 19th, 2005, 10:05
  4. Delete rows routine skips a row after delete
    By wbsmith in forum EXCEL HELP
    Replies: 1
    Last Post: September 17th, 2004, 04:08

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