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.

I have

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?

Answers


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.


Need Your Help

How do I implement background service for kontakt.io sdk?

android ibeacon ibeacon-android android-ibeacon

If I enter a region of a beacon with my Android device I like to show a notification. Thats well documented at Android SDK Quickstart

Regular expression for getting first and last name ignoring middle names

javascript regex

I'm searching a regular expression that could give me the first and last name in a string that is a complete name.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.