Most efficient way to hide all rows in which a certain value occurs
The Excel auto filter is not properly working if there is more than one row for headings, and it is also not assignable to specific columns, only. So I want to filter by VBA macro.
Sheet2.Range("A1:A40").Find(what:="Software", _ After:=Cells(4, 1), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False _ ).EntireRow.Hidden = True
But this only hides the row with the first occurance of "Software". Is there no way to use .find for that or do I have to use a loop?
If your range isn't super big, you can always loop through it, checking for a value, and hiding if found:
Sub test() Application.ScreenUpdating = False Dim lastRow As Integer, i As Integer Dim rng As Range, cel As Range lastRow = Sheet2.UsedRange.Rows.Count For i = lastRow To 1 Step -1 If Cells(i, 1).Value = "Software" or cells(i,1).Value = "software" Then Cells(i, 1).EntireRow.Hidden = True End If Next i Application.ScreenUpdating = True End Sub
Note: the software part is case sensitive, which is why I used Or.