Results 1 to 4 of 4

Thread: [RESOLVED] [Excel] Repeat for every worksheet in workbook

  1. #1

    Thread Starter
    Member
    Join Date
    Oct 2008
    Location
    South Africa
    Posts
    32

    Resolved [RESOLVED] [Excel] Repeat for every worksheet in workbook

    Hi guys,

    Some help please. I am trying to get my module to run through every worksheet in the workbook and delete all the hidden rows.

    My code doesn't work. It runs through the entire module without an error, but does nothing. It doesn't delete any rows and doesn't swith through the worksheets. If I break the loop, the worksheet name is empty.

    What am I missing?

    Here is the code:
    Code:
    Sub DeleteHiddenRows()
    
    Dim dataSheet As Worksheet
    
    
    For Each dataSheet In ThisWorkbook.Worksheets
       Dim lstRow As Integer
        
        lstRow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
            Dim i As Integer
            For i = lstRow To 1 Step -1
                If Cells(1, i).EntireRow.Hidden Then
                    Cells(1, i).EntireRow.Delete
                End If
            Next i
    Next dataSheet
    
    End Sub
    Thanks

  2. #2
    Hyperactive Member
    Join Date
    Jun 2006
    Location
    Best Place on Earth
    Posts
    363

    Re: [Excel] Repeat for every worksheet in workbook

    Hi Ossewa

    Simply using a For Each loop to cycle through the sheets does not help if you
    do not use the reference to the sheet in your code.

    You will also have a problem as you put your i variable which you used to
    refer to the row in the column position of Cells.

    Try the following.

    Code:
    Sub DeleteHiddenRows()
    Dim dataSheet As Worksheet
    Dim lstRow As Integer
    Dim Row As Integer
    
        For Each dataSheet In ThisWorkbook.Worksheets
            lstRow = dataSheet.UsedRange.Rows.Count
            For Row = lstRow To 1 Step -1
                If dataSheet.Cells(Row, 1).EntireRow.Hidden Then
                    dataSheet.Cells(Row, 1).EntireRow.Delete
                End If
            Next Row
        Next dataSheet
    
    End Sub
    One Final point, altthough VBA allows you to declare variables at any point
    in your code, it is bad practice to do this. You should make sure that you
    declare all variables at the start of the Sub or Function as it makes it easier to see them.
    Signature Under Construction

  3. #3
    Head Hunted anhn's Avatar
    Join Date
    Aug 2007
    Location
    Australia
    Posts
    3,669

    Re: [Excel] Repeat for every worksheet in workbook

    A note on the last used row number:
    Code:
    lstRow = dataSheet.UsedRange.Rows.Count
    may not give you the last row if the first row of the sheet is blank.
    That should be:
    Code:
    lstRow = dataSheet.UsedRange.Row + dataSheet.UsedRange.Rows.Count -1
    • Don't forget to use [CODE]your code here[/CODE] when posting code
    • If your question was answered please use Thread Tools to mark your thread [RESOLVED]
    • Don't forget to RATE helpful posts

    • Baby Steps a guided tour
    • IsDigits() and IsNumber() functions • Wichmann-Hill Random() function • >> and << functions for VB • CopyFileByChunk

  4. #4

    Thread Starter
    Member
    Join Date
    Oct 2008
    Location
    South Africa
    Posts
    32

    Re: [Excel] Repeat for every worksheet in workbook

    It works very good now - Thanks

Posting Permissions

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



Click Here to Expand Forum to Full Width