Hide rows in multiple worksheets based on a condition using a macro in vba

[MISS_AJAZ]

Hi,

I would like to create a macro for the following scenario, (I have used the worksheet names as per how they are in my workbook):

I would like to hide rows in column A with the word 'No' in, I need this to link to these 3 worksheets: 1st fit, 2nd fit, 3rd fit.

There are other worksheets within that workbook which I do not want linking.

I hope above makes sense and someone will be able to help me!

Thank you

 

[Michael M]

How about with autofilter

Sub MM1()
Dim ws As Worksheet, mray As Variant
mray = Array("1st fit", "2nd fit", "3rd fit")
For Each ws In Sheets(mray)
    ws.Activate
    Columns("A:A").AutoFilter Field:=1, Criteria1:="<>No"
Next ws
End Sub

 

[MISS_AJAZ]

Thank you Michael that worked , however I want to see if there is a way of doing it without using the filter.
I will resort to using the filter option if I can't find any other way.

Many thanks

 

[Michael M]

Ok, try

Sub MM1()
Dim ws As Worksheet, mray As Variant, lr As Long, r As Long
mray = Array("1st fit", "2nd fit", "3rd fit")
For Each ws In Sheets(mray)
    ws.Activate
    lr = Cells(Rows.Count, "A").End(xlUp).Row
        For r = lr To 1 Step -1
            If Range("A" & r).Value = "No" Then Rows(r).Hidden = True
         Next r
Next ws
End Sub

 

[MISS_AJAZ]

That didn't work

 

[Michael M]

Works fine for me
Where did you put the code ??
AND
are the sheets named exactly as they are in the code ??
AND
is "No" the only word in the cell ?

 

[MISS_AJAZ]

I put it into module 4 in VBA, yes the sheets are named exactly as they are in the code, and 'No' is the only word in the cell.
Hmm I wonder what I'm doing wrong.

 

[MISS_AJAZ]

This is the code I have used for making it work in just one worksheet and it works fine. Any way of editing this one so it links to all 3 worksheets?


Sub HideRows()
Dim cell As Range
For Each cell In Range("a:a")
If UCase(cell.Value) = "NO" Then
cell.EntireRow.Hidden = True
End If
Next
End Sub

 

[Michael M]

your first post suggested Proper case for "No"....you should be more specific

option compare text
Sub MM1()
Dim ws As Worksheet, mray As Variant, lr As Long, r As Long
mray = Array("1st fit", "2nd fit", "3rd fit")
For Each ws In Sheets(mray)
    ws.Activate
    lr = Cells(Rows.Count, "A").End(xlUp).Row
        For r = lr To 1 Step -1
            If Range("A" & r).Value = "NO" Then Rows(r).Hidden = True
         Next r
Next ws
End Sub

 

[MISS_AJAZ]

Ahh I get it! It's working now Thank you
Is there any way I can add in 'no', 'NO' and 'No'? So that it will still work if either are typed in?