+ Reply to Thread
Results 1 to 6 of 6

Deleting Rows and Columns Throughout All Worksheets

Hybrid View

  1. #1
    Registered User
    Join Date
    06-26-2015
    Location
    St.Paul, Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    37

    Deleting Rows and Columns Throughout All Worksheets

    Hi,

    I get a monthly set of data spread out through 5 sheets in a workbook. All the data on each sheet is categorized in the same way, that is, they are all in the same row and column throughout the sheets. I want to delete Rows 1, 2, and 3, and Columns A, B, and G, throughout all all of the sheets. I also want to Un-merge every cell within the workbook. I'm trying to record a macro, but it doesn't seem to replicate properly into a new data set. Any help would be greatly appreciated. Thanks!

  2. #2
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Deleting Rows and Columns Throughout All Worksheets

    Hi
    Try this code
    Sub Test()
        Dim SH As Worksheet
        Application.ScreenUpdating = False
            For Each SH In ThisWorkbook.Sheets
                SH.Rows("1:3").EntireRow.Delete
                SH.Columns("A:C").EntireColumn.Delete
                SH.Cells.UnMerge
            Next
        Application.ScreenUpdating = True
    End Sub
    Last edited by YasserKhalil; 07-16-2015 at 04:48 PM.
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  3. #3
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Deleting Rows and Columns Throughout All Worksheets

    Should be

    Option Explicit
    
    Sub Test()
        Dim SH As Worksheet
        Application.ScreenUpdating = False
            For Each SH In ThisWorkbook.Sheets
                SH.Rows("1:3").EntireRow.Delete
                SH.Columns("A:C").Delete
                SH.Cells.UnMerge
            Next
        Application.ScreenUpdating = True
    End Sub
    
     'OR
    Sub Test1()
        Dim SH As Worksheet
        Application.ScreenUpdating = False
            For Each SH In ThisWorkbook.Sheets
                With SH
                  .Rows("1:3").EntireRow.Delete
                  .Columns("A:C").Delete
                  .Cells.UnMerge
                End With
            Next
        Application.ScreenUpdating = True
    End Sub

  4. #4
    Registered User
    Join Date
    06-26-2015
    Location
    St.Paul, Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Deleting Rows and Columns Throughout All Worksheets

    I'm trying to delete columns A, B, and G, not C. Sorry for any confusion

  5. #5
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Deleting Rows and Columns Throughout All Worksheets

    Option Explicit
    
    Sub Test1()
        Dim SH As Worksheet
        Application.ScreenUpdating = False
            For Each SH In ThisWorkbook.Sheets
                With SH
                  .Rows("1:3").Delete
                  Union(.Columns("A:B"), .Columns("G")).Delete
                  .Cells.UnMerge
                End With
            Next
        Application.ScreenUpdating = True
    End Sub

  6. #6
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Deleting Rows and Columns Throughout All Worksheets

    Then try this
    Sub Test()
        Dim SH As Worksheet
        Application.ScreenUpdating = False
            For Each SH In ThisWorkbook.Sheets
                SH.Rows("1:3").EntireRow.Delete
                SH.Range("A:C,G:G").EntireColumn.Delete
                SH.Cells.UnMerge
            Next
        Application.ScreenUpdating = True
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] deleting columns in worksheets
    By aaaaa34 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-24-2014, 09:29 AM
  2. Deleting Empty Columns from all Worksheets
    By DonaldTerry in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-08-2012, 09:19 AM
  3. Deleting columns in all Worksheets till u get value
    By vicky_excel in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-23-2010, 03:53 AM
  4. Macros deleting certain worksheets, columns and rows
    By Steezo in forum Excel General
    Replies: 0
    Last Post: 08-01-2005, 05:28 AM

Tags for this Thread

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