Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-16-2019, 07:41 AM   #1
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 637
Thanks: 13
Thanked 9 Times in 8 Posts
smiler44 is on a distinguished road
macro to hide rows if column

I am in need of a macro to hide rows where column C does not contain
and of these values, there will only be 1 value per cell.
book, car, plane, boat, pencil, rubber, rule, pen.

lots of macros to hide rows that do contain criteria but could not find one or get one to work where I had 7 items to leave visible and hide the rest

thank you smiler44

smiler44 is offline   Reply With Quote
Old 03-16-2019, 08:31 AM   #2
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 1,938
Thanks: 433
Thanked 284 Times in 249 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: macro to hide rows if column

You can exclude those items by using the Not In() Function in a Query.

Give this a look over:

https://support.office.com/en-us/art...3-aba746fb29d8
__________________
- Ciao, John
NauticalGent is offline   Reply With Quote
Old 03-16-2019, 08:38 AM   #3
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 637
Thanks: 13
Thanked 9 Times in 8 Posts
smiler44 is on a distinguished road
Re: macro to hide rows if column

Quote:
Originally Posted by NauticalGent View Post
You can exclude those items by using the Not In() Function in a Query.

Give this a look over:

https://support.office.com/en-us/art...3-aba746fb29d8
thank you, either I did not understand what I was looking at or what I'm after was not there

smiler44

smiler44 is offline   Reply With Quote
Old 03-16-2019, 08:42 AM   #4
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 1,938
Thanks: 433
Thanked 284 Times in 249 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: macro to hide rows if column

The article (and I) assume(s) you are familiar with queries. If this is not the case then there are some basics you need to learn before you start dabbling with macros and VBA.

ARE you familiar with queries?
__________________
- Ciao, John
NauticalGent is offline   Reply With Quote
Old 03-16-2019, 08:44 AM   #5
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 637
Thanks: 13
Thanked 9 Times in 8 Posts
smiler44 is on a distinguished road
Re: macro to hide rows if column

I think I've just had a lightbilb moment.
this is a modified macro I found on the internet

Code:
BeginRow = 2
    EndRow = 20
    ChkCol = 3
    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value <> "Brand House" And Cells(RowCnt, ChkCol).Value <> "Local Brand Family Group" Then 
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        Else
            Cells(RowCnt, ChkCol).EntireRow.Hidden = False
        End If
    Next RowCnt
this seems to work for 2 criteria's i'll try with 7

smiler44
smiler44 is offline   Reply With Quote
Old 03-16-2019, 08:46 AM   #6
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 1,938
Thanks: 433
Thanked 284 Times in 249 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: macro to hide rows if column

My apologies...I assumed you were using Access. Sorry mate, I can’t help you.
__________________
- Ciao, John
NauticalGent is offline   Reply With Quote
Old 03-16-2019, 10:44 AM   #7
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 637
Thanks: 13
Thanked 9 Times in 8 Posts
smiler44 is on a distinguished road
Re: macro to hide rows if column

Quote:
Originally Posted by NauticalGent View Post
My apologies...I assumed you were using Access. Sorry mate, I canít help you.
no worries thank you fr trying

smiler44

smiler44 is offline   Reply With Quote
Old 03-16-2019, 10:47 AM   #8
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 637
Thanks: 13
Thanked 9 Times in 8 Posts
smiler44 is on a distinguished road
Re: macro to hide rows if column

Quote:
Originally Posted by NauticalGent View Post
The article (and I) assume(s) you are familiar with queries. If this is not the case then there are some basics you need to learn before you start dabbling with macros and VBA.

ARE you familiar with queries?
not sure i understand the question
count myself as an amateur that has hook or by crook created some very successful bits of automation.
Google is my friend but even then I do find I need personal help
and this is where I turn to a form

smiler44
smiler44 is offline   Reply With Quote
Old 03-16-2019, 12:19 PM   #9
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,095
Thanks: 421
Thanked 753 Times in 731 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: macro to hide rows if column

I'd start by recording a macro. That would probably do all you need if you already have one of each value. If not, just edit the macro, the change should be obvious.

Quote:
Originally Posted by smiler44 View Post
I am in need of a macro to hide rows where column C does not contain
and of these values, there will only be 1 value per cell.
book, car, plane, boat, pencil, rubber, rule, pen.

lots of macros to hide rows that do contain criteria but could not find one or get one to work where I had 7 items to leave visible and hide the rest

thank you smiler44

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Concatenate Column Values from Multiple Rows into a Single Column erncelen Queries 50 12-14-2018 10:20 AM
Re: Concatenate Column Values from Multiple Rows into a Single Column Snuffy94 Queries 2 07-19-2012 08:54 AM
How to hide rows in a report? mattey Reports 8 08-23-2011 02:33 PM
Code to hide specific rows? laxster Excel 2 06-02-2010 10:31 AM
Hide rows in multiple worksheets with Macro function okeane Excel 5 08-22-2008 07:22 AM




All times are GMT -8. The time now is 05:53 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World