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" wrote:
> 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" wrote:
>
> > 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
> >
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Stephen" wrote:
> >
> > > 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
> > >
|
Similar Threads
