Excel VBA Hide rows that don't contain value

[TattyJJ]

Hi,

I have produced the below code that hides rows if they contain specific values which works fine, but now i want to do the opposite and hide all rows that DON'T contain the same values.

Rows.EntireRow.Hidden = False

    BeginRow = 2
    EndRow = Cells(Rows.Count, "A").End(xlUp).Row
    ChkCol = 1

    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value Like "ZZZZ*" Or Cells(RowCnt, ChkCol).Value Like "ENDO005" Or Cells(RowCnt, ChkCol).Value Like "ENDO007" Or Cells(RowCnt, ChkCol).Value Like "ENDO009" Or Cells(RowCnt, ChkCol).Value Like "ENDO011" Or Cells(RowCnt, ChkCol).Value Like "ORTO020" Or Cells(RowCnt, ChkCol).Interior.ColorIndex = 6 Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
            Else
            Cells(RowCnt, ChkCol).EntireRow.Hidden = False
        End If
    Next RowCnt

I was though it would be as simple as swapping the true and false over, but that doesn't seem to work and i'm not sure how to go about making it do what i want.

Anyone able to help?

Thanks

[#2]

@TattyJJ 

May I ask why you are writing a VBA routine to do something that a simple Data....Filter... will accomplish? That you're writing a VBA routine at all tells me that you're knowledgeable about Excel, so I'm going to guess that there is a reason; I'm just curious what it is.

[#3]

@mathetes 

I thought about this, but it won't really work.

This bit of code is part of a much larger macro that automates a whole load of repetitive tasks. 

Thanks

[#4]

@TattyJJ 

Well, let me ask my question in a different way, then. Perhaps first a bit of background as to why I'm asking.

I became a skeptic on the subject of macros and VBA routines a number of years ago. I was the director of the HR/Payroll database for a major corporation, and one important but fairly minor recurring responsibility we had was to prepare a monthly report to top management on headcounts, by division, by employment category (P/T, F/T, temp, etc), by location.....   The IT people had written a very elaborate VBA routine that manipulated the existing spreadsheet data into the format required for the report. I don't recall exactly how long it took to run, but for sake of illustration let's say it was 45 minutes (it was in that order of magnitude). I created a process that simply used Excel's built in functions for the summarization of database tables... mine worked virtually instantaneously. Granted, you had to get the data first, so add a few minutes, but it worked, quickly and reliably. I came away from that experience knowing that, yes, there are times when a macro or VBA routine is needed; but also convinced that Excel has built in many functions that reliably and efficiently do most of what we want to do in transforming raw data into a meaningful report.

So if you'll humor me--I recognize there may still be a fully valid reason for the use of VBA; I don't categorically reject it; I just question it--what is it that you're trying to accomplish in going from whatever raw data you have to whatever finished output? And are you sure that there's no capability already built-in that could do it?

And please, feel free to ignore the question if you so desire. My intent certainly is not to irritate you; you're clearly very competent and may have completely valid reasons, whereas my experience may have unreasonably prejudiced me against VBA....so you can help me broaden my perspective.

[#5]

@mathetes 

Granted the macro i have created (managed to solve the issue in the end) is a bit slow, but i believe this is a small price to pay for automating was was previously a very tedious and complex set of tasks.
It is also worth noting this isn't for me to use, it is to allow people who have a very limited knowledge of excel to complete what would otherwise be a difficult set of tasks.

Essentially we have a piece of software that outputs an excel file in order to produce a series of reports from.
What comes out is quite a mess, formatting all over the place, columns hidden that need to be visible, columns and rows that need to be deleted etc etc.
First job of the macro is to tidy that all up so it looks nice.

Secondly, and this is where the code i was asking about comes in, it prompts for information in order to show the specific data that is needed for each report.

While this could be done by formatting it as a table and filtering, there are literally thousands of them, to go through and pick out just the handful that are needed, then repeat a dozen times for the different sets of data, that is a real hassle.

I hope this explains my reasoning for doing things the way i am.