1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel Macro -> Delete Multiple Rows

Discussion in 'Business Applications' started by computerman29642, Nov 18, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    I am trying to find a way to find a specific text within a worksheet, select that cell and the 9 rows under that row, and delete the 10 rows.
     
  2. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    I forgot to mention that the code will actually run from a different worksheet.

    So, sheet two will actual contain the code to delete the rows from sheet 1.
     
  3. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    Hey C'man.

    try this...

    Code:
    Sub Macro1()
    This = "0.00169909838200311" ' Change this value to whatever you want to search for
    Rows(Cells.Find(What:=This).Row & ":" & Cells.Find(What:=This).Row + 9).Delete Shift:=xlUp
    End Sub
     
    
    You can have it on any woorkbnook you want and run it on anyworkbook you want also long as you Activate the workbook first before running it.
     
  4. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    As usual...

    NB. Make backup of stuff before testing code
     
  5. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Thanks turbodante. Is there a way to change this portion of the code
    Code:
    & Cells.Find(What:=This).Row + 9).Delete Shift:=xlUp 
    to find the next blank cell?

    Also, is there not a way to specify the worksheet within the code instead of activating the worksheet?

    Something like this
    Code:
     wsTest.Rows(Cells.Find(What:=This).Row
    Where wsTest is set/assigned to a specific worksheet name.
     
  6. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    I don't understand.

    • is the code deleting a row short,
    • or do you want to include from the match to down 10 rows, plus additional to next blank
     
  7. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    The code is falling a row short. There will always be a blank row betwene the rows being deleted, and the rows below the ones being deleted.
     
  8. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    Just so I understand...

    If your match is in row 10, you want to delete rows

    10,11,12,13,14,15,16,17,18,19 & 20 that's 11 rows?
     
  9. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    That is correct. The reason for looking for the blank row is to be sure that all the rows in the correct range are selected.

    I really did not want to specify teh number of cells because you never know if that number will increase.
     
  10. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    turbodante, I believe I figured out the code. Please take a look below:

    Code:
    Rows(Cells.Find(What:=This).Row & ":" & Cells.Find(What:="", After:=ActiveCell).Row).Select
    
    I used select for testing. What do you think?
     
  11. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    try this one...

    It will delete from the matching row to the farthest blank row on the same column. NB. It may be that you have more than 1 blank, so it finds the blank before the next cell that contains a non-blank.

    Code:
    Sub Macro4()
    Dim Rw1 As Long, Rw2 As Long
    Set xSheet = Sheets("Sheet1")
    This = "here is what you want to find"  ' change this to what you need to match
    On Error Resume Next
       Rw1 = Cells.Find(What:=This).Row
       Rw2 = Cells.Find(What:=This).End(xlDown).End(xlDown).Offset(-1, 0).Row
     
    xSheet.Rows(Rw1 & ":" & Rw2).Delete Shift:=xlUp
     
    End Sub
     
    
     
  12. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Nevermind, the code I posted does not work properly after further testing.

    turbodante, I am unable to get your new code to work.
     
  13. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744

    What errors are you getting? Can you post a sample of your data?
     
  14. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    I was finally able to get the code to work, but it is still selecting on row short.
     
  15. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744

    Can you check if the very last blank cell truly blank. ie. is not " ", or a space character?
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/878504

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice