Forum Discussion

TattyJJ's avatar
TattyJJ
Copper Contributor
Apr 07, 2020

Excel VBA Hide rows that don't contain value

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

excel
Macros and VBA

4 Replies

  • mathetes's avatar
    mathetes
    Gold Contributor
    Apr 07, 2020

    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.

    • TattyJJ's avatar
      TattyJJ
      Copper Contributor
      Apr 07, 2020

      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

      • mathetes's avatar
        mathetes
        Gold Contributor
        Apr 07, 2020

        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.

         

Resources