Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Feb 2014
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    moving blank tabs

    hi folks

    my financial reporting software can produce reports with what it calls trees. Each branch of a tree can be a sub set of the database. Some branchs of the tree are blank. The software can also send reports to excel. In the spreadsheet you get one sheet or tab for each branch of the tree. the software is not able to not export a blank branch of the tree.

    is there something in excel that can seek out the blank tabs and move them to the end of the workbook?

    thanks!

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi ianr

    ..yes, blank tabs can be moved to the end of the workbook, or deleted if preferred.
    I presume the blank tabs have sheet names but 'no data' on the sheets themselves?
    If this is the case, a short macro (vba routine) could be created to do what you want, and assigned to a keyboard-shortcut.

    Let us know what defines a 'blank tab' and we'll show you how to do it.

    zeddy

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If blank tabs is when nothing is on the sheet then this will work:

    Code:
    Option Explicit
    Sub MoveBlankSheets()
      Dim iShtCount As Integer
      Dim i As Integer
      iShtCount = Worksheets.Count
      For i = iShtCount To 1 Step -1
        If Application.WorksheetFunction. _
          CountA(Worksheets(i).Cells) = 0 Then
          Worksheets(i).Move after:=Worksheets(iShtCount)
        End If
      Next
    End Sub
    or to just delete them:
    Code:
    Option Explicit
    Sub DeleteBlankSheets()
      Dim iShtCount As Integer
      Dim i As Integer
      iShtCount = Worksheets.Count
      For i = iShtCount To 1 Step -1
        If Application.WorksheetFunction. _
          CountA(Worksheets(i).Cells) = 0 Then
          Application.DisplayAlerts = False
          Worksheets(i).Delete
          Application.DisplayAlerts = True
        End If
      Next
    End Sub
    Steve

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Try the following code. It will move any sheet with no data to the end.

    HTH,
    Maud

    Code:
    Sub Macro1()
    On Error GoTo Errorhandler
    For I = 1 To Worksheets.Count
        If Sheets(I).UsedRange = "" Then
            Sheets(I).Select
            Sheets(I).Move After:=Sheets(Worksheets.Count)
        End If
    Errorhandler:
    Next I
    End Sub

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Maudibe,
    You need to go from Worksheets.Count to 1 Step -1 or you will miss worksheets. After any sheet is moved the next sheet will not be tested

    For example, If both Sheets(1) and Sheets(2) are blank, your code will move sheets(1) to the end, then increment I to 2, but after moving sheets(1) it becomes sheets(N), and sheets(2) will become sheets(1), so will be skipped as you will look at the new sheets(2) [Which was the former sheets(3)] it will remain at the start.

    [Probably not a problem with this example, but it is a bad practice and a bad habit to get into. It is better to use Sheets.count with sheets(I) or use worksheets.count with worksheets(I) to maintain consistency. Having chartsheets will screw up the count. You loop through the worksheets.count (which does not include a count of any chartsheets), but you use Sheets(I) which includes chart sheets.]

    Steve

  6. #6
    New Lounger
    Join Date
    Feb 2014
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    hi folks

    thanks for the suggestions.

    yes the tabs have names. Each tab has a header and footer. Each tab will have column headings so the first three rows will have something in them.


    so I guess not quite blank / empty tabs

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    In my examples, instead of:
    Code:
        If Application.WorksheetFunction. _
          CountA(Worksheets(i).Cells) = 0 Then
    You could use:
    Code:
        If Application.WorksheetFunction. _
          CountA(Worksheets(i).Rows("4:" & Worksheets(i).Rows.Count)) = 0 Then
    Steve

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    ianr,

    As an alternative, the following code examines the range between the header and footer of each sheet for data. If it is blank, it moves it to the end. The code assumes that you have a 3 row header and the footer at the bottom has at least some data in column A else change the column where indicated.

    Code:
    Sub MoveSheet()
    Dim Rng As Range
    For I = 1 To Worksheets.Count
        LastRow = Worksheets(I).Cells(Rows.Count, 1).End(xlUp).Row 'CHANGE THE 1 TO A COLUMN THAT HAS
            'CONSISTANT DATA FOR THE FOOTER    
        Set Rng = Worksheets(I).Rows("4:" & LastRow - 1)
        If WorksheetFunction.Aggregate(3, 4, Rng) <> 0 Then
            Worksheets(I).Move Before:=Sheets(1)
        End If
    Next I
    End Sub
    Last edited by Maudibe; 2014-02-28 at 22:20.

Posting Permissions

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