-
Oct 8th, 2008, 02:14 AM
#1
Thread Starter
Member
[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
-
Oct 8th, 2008, 02:47 AM
#2
Hyperactive Member
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
-
Oct 8th, 2008, 03:13 AM
#3
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
-
Oct 8th, 2008, 03:24 AM
#4
Thread Starter
Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|