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

MISS_AJAZ

New Member
Joined
Jun 23, 2014
Messages
17
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 :biggrin:
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,578
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
How about with autofilter
Code:
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

New Member
Joined
Jun 23, 2014
Messages
17
Thank you Michael that worked :biggrin:, 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

Well-known Member
Joined
Oct 27, 2005
Messages
20,578
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Ok, try
Code:
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

New Member
Joined
Jun 23, 2014
Messages
17

ADVERTISEMENT

That didn't work :(
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,578
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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

New Member
Joined
Jun 23, 2014
Messages
17

ADVERTISEMENT

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

New Member
Joined
Jun 23, 2014
Messages
17
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,578
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
your first post suggested Proper case for "No"....you should be more specific
Code:
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

New Member
Joined
Jun 23, 2014
Messages
17
Ahh I get it! It's working now :biggrin: :biggrin: 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?
 

Forum statistics

Threads
1,136,968
Messages
5,678,866
Members
419,787
Latest member
juanam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top