Code that sort of works

[Stephen]

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect Password:="******"
ws.Columns("A:W").AutoFit

Dim iLastrow As Integer
'This is your last row in col a
iLastrow = Range("A910").End(xlUp).Row
For i = 30 To iLastrow
If Cells(i, 1).Value = "" And Cells(i, 1).EntireRow.Hidden = False Then
Cells(i, 1).EntireRow.Hidden = True
End If
Next i

Next ws

I've got 12 sheets. Sheet 1 works with the exception of the last 5 rows
(they do not get hidden), and sheets 2-12 have formula's that simply do
this...
=IF(January!A395="","",January!A395)

What am I missing/doing wrong?

Thanks

 

[Jim Thomlinson]

You are using a worksheet object to move through your sheets. But you are not
always referencing your code back to that object. If you do not specify then
it defaults to the active sheet. Try something more like this...

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
with ws
..Unprotect Password:="******"
..Columns("A:W").AutoFit

Dim iLastrow As Integer
'This is your last row in col a
iLastrow = .Range("A910").End(xlUp).Row
For i = 30 To iLastrow
If .Cells(i, 1).Value = "" And .Cells(i, 1).EntireRow.Hidden = False Then
..Cells(i, 1).EntireRow.Hidden = True
End If
Next i
end with
Next ws

 

[Stephen]

Nope still works the same way.

What I'm attempting to do is hide every row where the cell in column A is
either blank or has a blank value. My worksheets are a fixed size ending at
row 910.

 

[Jim Thomlinson]

Because your cells are filled with formulas you can not use xlUp. It will go
all the way to the top (row 30). Try this... (If the performance is poor you
may want to turn off autocalc at the start and back on at the end. If you
still need better performance than that then there are ways to improve it
further...)

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
with ws
..Unprotect Password:="******"
..Columns("A:W").AutoFit

For i = 30 To 910
If .Cells(i, 1).Value = "" And .Cells(i, 1).EntireRow.Hidden = False Then
..Cells(i, 1).EntireRow.Hidden = True
End If
Next i
end with
Next ws

 

[JLGWhiz]

One way to trouble shoot the five rows on the first sheet is to insert a
message box between Next i and Next ws like:

MsgBox Cells(i, 1).Value

to make sure it gets to 910 and that A910 = "".

I also think I would step through the code for the other sheets to see what
values the cells are showing on those sheets.

If it is working for all but five rows on sheet 1 then the last five rows
values are not equal to "" or it is not making it to those rows to read them
(maybe more hidden rows than you think). First, unhide all rows.

 

[broro183]

Hi Stephen,

Do you have any auto or advanced filtering on the sheets in your workbook?
If not, the below may work:

Sub DeletingRows()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
With ws
..Unprotect Password:="******"
..Columns("A:W").AutoFit
..AutoFilterMode = False 'needed in case another range is already autofiltered
..Range("a30:W910").AutoFilter Field:=1, Criteria1:="<>"
End With
Next ws
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

If this works for you, you may want to include some error around checking
for existing filtering. This saves the need for looping & will probably be
faster to run (esp with the manual calc as suggested by Jim & turning the
screen updating off too).

Also, there is another way of protecting your worksheets for the "user
interface only" which you could easily use with no impact on users as there
are macros in the file already. To see the other arguments/parameters that
can be set when protecting sheets, select "protect" & press [F1] in the VB
Editor. My understanding is that the "user interface" option needs to be eset
every time the file is opened which is I've put it in the workbook window:

'copy this into the "thisworkbook" code window
Option Explicit
Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect UserInterfaceOnly:=True
Next ws
End Sub

hth
Rob

Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[Stephen]

Fantastic

I figured out the problem with the original code as you modified it for me -
I needed to expand my range to row 912. But this new loop makes much shorter
work of the job and accounts for only the rows that are necessary for
evaluation.

Thanks a bunch!!!