Code for finding a specific value within a range

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
458
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
The code that I currently have works... But, I just quickly put it together in order to test my 'concept' (which is the code directly below the If Cells(88, 1) = "MTHLY" part...) Now that I know that it works, I need to replace the If Cells(88, 1) = "MTHLY" part of the code. Currently that code references a specific cell "(88, 1)" to where it is searching for the string ("MTHLY"). I need the new code to look within a range between anywhere starting in column A, row 15... and down to the last row in that column that contains any data...

Here is my current code:

VBA Code:
If Cells(88, 1) = "MTHLY" Then
'*********
        For Each Cell In Range(Cells(2, 26), Cells(2, PCol)).SpecialCells(xlCellTypeVisible)
            If Cell Like "*Sa*" Then
                    Columns(Cell.Column).Hidden = True
            Else
                    Columns(Cell.Column).Hidden = False
            End If
       Next
'*********
        For Each Cell In Range(Cells(5, 26), Cells(5, PCol)).SpecialCells(xlCellTypeVisible)
            If Cell Like "*P*" Then
                    Columns(Cell.Column).Hidden = True
            Else
                    Columns(Cell.Column).Hidden = False
            End If
       Next
'*********
    Else
    End If
'*********

A couple things... the range that the code will be searching will only contain one visible row- in my example that row is 88; but it could be any row (any row number below 15, that is.
And there will always be just one row remaining in the range (never more than one, and just one.)

Column A is unhidden for the screen shot so that you can see where the "MTHLY" is located.
Filter5.jpg


Thank you for any suggestions!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
I had a similar task a while back, try;

VBA Code:
Dim LastRow As Long

For LastRow = Columns("A").SpecialCells(xlCellTypeLastCell).Row To 1 Step -1
    If Len(Cells(LastRow, "A").Formula) Then Exit For
Next

For Each Cell In Range("A15:A" & LastRow)
    If Cell.Value = "MTHLY" Then
        For Each Cell In Range(Cells(2, 26), Cells(2, PCol)).SpecialCells(xlCellTypeVisible)
            If Cell Like "*Sa*" Then
                Columns(Cell.Column).Hidden = True
            Else
                Columns(Cell.Column).Hidden = False
            End If
        Next
      
        For Each Cell In Range(Cells(5, 26), Cells(5, PCol)).SpecialCells(xlCellTypeVisible)
            If Cell Like "*P*" Then
                Columns(Cell.Column).Hidden = True
            Else
                Columns(Cell.Column).Hidden = False
            End If
        Next
    End If
Next
 
Upvote 0
Solution
I had a similar task a while back, try;

VBA Code:
Dim LastRow As Long

For LastRow = Columns("A").SpecialCells(xlCellTypeLastCell).Row To 1 Step -1
    If Len(Cells(LastRow, "A").Formula) Then Exit For
Next

For Each Cell In Range("A15:A" & LastRow)
    If Cell.Value = "MTHLY" Then
        For Each Cell In Range(Cells(2, 26), Cells(2, PCol)).SpecialCells(xlCellTypeVisible)
            If Cell Like "*Sa*" Then
                Columns(Cell.Column).Hidden = True
            Else
                Columns(Cell.Column).Hidden = False
            End If
        Next
     
        For Each Cell In Range(Cells(5, 26), Cells(5, PCol)).SpecialCells(xlCellTypeVisible)
            If Cell Like "*P*" Then
                Columns(Cell.Column).Hidden = True
            Else
                Columns(Cell.Column).Hidden = False
            End If
        Next
    End If
Next
Excellent. Thank you. That is EXACTLY what I needed to make this work (y)
 
Upvote 0

Forum statistics

Threads
1,215,261
Messages
6,123,942
Members
449,134
Latest member
NickWBA

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
Back
Top