+ Reply to Thread
Results 1 to 12 of 12

How to delete rows with specific value for multiple sheets

  1. #1
    Registered User
    Join Date
    09-11-2012
    Location
    canada
    MS-Off Ver
    Excel 2010
    Posts
    6

    How to delete rows with specific value for multiple sheets

    Hi everyone,

    I try to create a simple VB code that can delete rows with "0" in colum B (from row 1700 to row 30) for mutiple sheets, starting from Sheet 8. I'm new at this forum and VB. I'm really appreciated for any help and suggestion. Here is the code,

    Function DeleteCompletelyBlankRows()

    Dim LastRow As Integer
    Dim n As Integer
    Dim k As Integer

    k = ActiveWorkbook.Worksheets.Count
    For i = 8 To k - 1 Step 1
    Sheets(i).Select

    LastRow = 1700
    For n = LastRow To 30 Step -1
    If Cells(n, "B").Value = 0 Then
    Cells(n, "B").EntireRow.Delete
    End If
    Next i
    End Function

    Thanks a lot!
    Jerry

  2. #2
    Registered User
    Join Date
    09-01-2012
    Location
    India
    MS-Off Ver
    Excel 2007/10
    Posts
    45

    Re: How to delete rows with specific value for multiple sheets

    try

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    09-11-2012
    Location
    canada
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: How to delete rows with specific value for multiple sheets

    Thanks. I tried it. It works until sheet 21. The message shows that "Run time error 1004. Select method of worksheet class failed." Do you have idea why? I think I have the similar situation when using my code.

    Thanks,

    Quote Originally Posted by pooja_deshpande View Post
    try

    Please Login or Register  to view this content.

  4. #4
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517
    Try
    Please Login or Register  to view this content.
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  5. #5
    Registered User
    Join Date
    09-11-2012
    Location
    canada
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: How to delete rows with specific value for multiple sheets

    The code for deleting the rows works well for one sheet, even for sheet 21. However, it cannot delete multiple sheets automatically. That's why I add sheets().select. Not quite sure what is the best way to activate each sheet in a workbook.

    Thanks,

    Quote Originally Posted by cnj_zeng View Post
    Thanks. I tried it. It works until sheet 21. The message shows that "Run time error 1004. Select method of worksheet class failed." Do you have idea why? I think I have the similar situation when using my code.

    Thanks,

  6. #6
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: How to delete rows with specific value for multiple sheets

    Give this a try

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    09-11-2012
    Location
    canada
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: How to delete rows with specific value for multiple sheets

    Thanks, Mike. It looks like get into a dead loop something like that and total dead.

    Jerry

    Quote Originally Posted by mike7952 View Post
    Give this a try

    Please Login or Register  to view this content.

  8. #8
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: How to delete rows with specific value for multiple sheets

    Try

    Please Login or Register  to view this content.

  9. #9
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: How to delete rows with specific value for multiple sheets

    Here's another way that deletes the rows at once

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    09-11-2012
    Location
    canada
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: How to delete rows with specific value for multiple sheets

    It still stops at page 21. I've tried another date file. It stops at the page 8 right away. Quite weird.

    Thanks,
    Jerry

    Quote Originally Posted by mike7952 View Post
    Try

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    09-11-2012
    Location
    canada
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: How to delete rows with specific value for multiple sheets

    Thank you Mike, I finally figured it out. As there are some hidden sheets in the workbook that I didn't notice, that cause the sheet # not continued. After I fixed those sheet #, the problem is gone. Your code is running like a bullet. I don't know how to tag this as solved, thanks a lot anyway!

    Jerry
    Quote Originally Posted by mike7952 View Post
    Here's another way that deletes the rows at once

    Please Login or Register  to view this content.

  12. #12
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,243

    Re: How to delete rows with specific value for multiple sheets

    On the this page, at the top, you see "Thread Tools", Click the drop down arrow and choose solved

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1