[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

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.

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

---

It works very good now - Thanks