1

When a button is pressed I want to loop through all the cells in my Sheet and find the cells that contains .doc or .xls or .pdf and hide the entire Row. I know I can't use Contains but there must be something similar.

Cell example PM-TR Training.doc

This is what I have for now what can I replace contains with?

Sub HideRows()
    Dim cell As Range
    Dim DataCount As Integer
    'Change the sheet name as necessary in the following line
    With Worksheets("Sheet1")
        DataCount = Range("A" & Rows.Count).End(xlUp).Row
        For Each cell In Range("A1:A" & DataCount)
            If cell.Contains(".doc") Or cell.Contains(".xls") Or cell.Contains(".pdf") Then
                'The following code assumes you want the row hidden.
                Range(cell.Row).EntireRow.Hidden = True
            End If
        Next cell
    End With
End Sub
phil652
  • 1,484
  • 1
  • 23
  • 48
  • possible duplicate of [Check if a string contains another string](http://stackoverflow.com/questions/15585058/check-if-a-string-contains-another-string) – Nicolas R Feb 23 '15 at 15:50
  • I saw this question before posting but I didn't really understand how to make it work for my case. Now I do thanks to user3561813 – phil652 Feb 23 '15 at 16:06
  • You could have mentioned it in your question in order to get a better help on the implentation, but the important thing is that you understand now – Nicolas R Feb 23 '15 at 16:09
  • I will make sure to do it next time, thank you. – phil652 Feb 23 '15 at 17:56
  • Would an autofilter work? Why use VBA for this? – RubberDuck Feb 25 '15 at 18:17
  • I don't think so, I have a button to hide the links to documents and another button to show them. – phil652 Feb 25 '15 at 18:57

1 Answers1

2

The function InStr withing your IF statement should do the trick.

IF instr(cell.value, ".doc")> 0 then
    'Code Here
basodre
  • 5,720
  • 1
  • 15
  • 23