Mega Quick VBA Amendement

MrMaker

New Member
Joined
Jun 7, 2018
Messages
28
Afternoon all,

Really quick one.

I have some code (below) that generally works fine (to filter sheets), however, I don't want it to run on a new sheet called 'Summary' and I am getting a debug error.
Can someone add a line to basically say 'ignore Summary sheet' and run only on "Sheet 1". "Sheet 2" and "Sheet 3"

Thank you in advance


VBA Code:
Private Sub Workbook_Open()

Dim Sht As Worksheet, R As Range

Application.ScreenUpdating = False

For Each Sht In Me.Worksheets

    If Sht.Name <> "Data Selection" Then

        Set R = Sht.Range("A1").CurrentRegion

        R.AutoFilter field:=1, Criteria1:="<>" & Sheets("Data Selection").Range("A1")

        On Error Resume Next

        Set R = R.Offset(1, 0).SpecialCells(xlCellTypeVisible)

        On Error GoTo 0

        Sht.AutoFilterMode = False

        If Not R Is Nothing Then R.EntireRow.Delete

    End If

    Sht.Rows.RowHeight = 25

Next Sht

With Sheets("Data Selection")

    .Range("A1").Value = .Range("A1").Value

    .Rows.RowHeight = 25

    .Visible = xlHidden

End With

Application.ScreenUpdating = True

End Sub
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,524
Office Version
365
Platform
Windows
How about
VBA Code:
If Sht.Name <> "Data Selection" And Sht.Name <> "Summary" Then
 

MrMaker

New Member
Joined
Jun 7, 2018
Messages
28
Thank you for your quick reply.

I then get a debug error with the following line in yellow

R.AutoFilter field:=1, Criteria1:="<>" & Sheets("Data Selection").Range("A1")

(For context, the cell A1 in 'Data Selection' is the criteria used to select the autofilters

Any ideas?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,524
Office Version
365
Platform
Windows
Do you have any data in & around A1 on the sheet when you get the error?
 

MrMaker

New Member
Joined
Jun 7, 2018
Messages
28
It just has a name in it, which then filters the three data tabs.
The filtering happens fine, but then something goes wrong.
I'm a VBA amateur so any help is appreciated :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,524
Office Version
365
Platform
Windows
Add this message box as shown
Rich (BB code):
For Each Sht In Me.Worksheets

    If Sht.Name <> "Data Selection" Then

        Set R = Sht.Range("A1").CurrentRegion
        MsgBox Sht.Name & vbLf & R.Address
        R.AutoFilter field:=1, Criteria1:="<>" & Sheets("Data Selection").Range("A1")
What does it say just before you get the error
 

MrMaker

New Member
Joined
Jun 7, 2018
Messages
28
It says:

Driver $A$1:$N$1448
Vehicle $A$1:$M$10
Collisions $A$1:$M$7
FAQs $A$1
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,524
Office Version
365
Platform
Windows
In that case the problem is with the sheet called FAQs, in that there is no data to filter
 

MrMaker

New Member
Joined
Jun 7, 2018
Messages
28
That's right, its just a screenshot of some FAQ's

How can I fix this?

What do the Driver/Vehicle/Collisions references mean? As There is more data in Collisons and Vehicle than that suggests....


Thank you
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,524
Office Version
365
Platform
Windows
They are the range of the current region.
If you have more data in those sheets it suggest that you have totally blank rows & or columns.
 

Watch MrExcel Video

Forum statistics

Threads
1,098,859
Messages
5,465,111
Members
406,412
Latest member
superjoejoe

This Week's Hot Topics

Top