Results 1 to 6 of 6

Thread: Seleting and delting blank rows on multiple WS
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Seleting and delting blank rows on multiple WS

    Hi guys. I am trying to run a macro to delete blank rows within a range on all the worksheets in a workbook. Here is what i got so far and is not working. thanks for any help you can provide.

    Code:
    Sub SheetTest()
    Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Sheets
        MsgBox ws.Name
       ActiveWindow.SmallScroll Down:=-72
       Range("D23:D127").Select
        Selection.SpecialCells(xlCellTypeBlanks).Select
       Selection.EntireRow.Delete
    
        Next ws
    
    End Sub

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,314
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Seleting and delting blank rows on multiple WS

    Hi & welcome to MrExcel.
    In what way isn't it working?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Dec 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Seleting and delting blank rows on multiple WS

    Thanks. It just does not move to the next sheet. and repeat.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,314
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Seleting and delting blank rows on multiple WS

    Try
    Code:
    Sub SheetTest()
    Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Sheets
       ws.Range("D23:D127").SpecialCells(xlBlanks).EntireRow.Delete
    Next ws
    
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Dec 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Seleting and delting blank rows on multiple WS

    It worked thanks

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,314
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Seleting and delting blank rows on multiple WS

    You're welcome
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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