Hide Rows that <> "string"

[dellzy]

Hi,
I found below formula in this forum that works really great on hiding all rows if it matches the string in any column of the row. However, I would like it to be reversed. Meaning, it will hide all rows that is NOT match to the string and also NOT blank (to avoid hiding rows of the entire worksheet). Also it needs to apply from row 5 then below. What I want to achieve is that I want to create a validation list consists of Dept name above row 5 and a table below it will only show the row records for the particular dept only. This is to avoid other users from other dept to meddle with records for other depts. Anyone can help,please? Thank you in advance.


Sub hiderows() For i = 1 To Range("A" & Rows.Count).End(xlUp).Row k = 0 For j = 1 To Range("A" & i).End(xlToRight).Column If InStr(1, Cells(i, j), "Circuit Total") > 0 Then k = k + 1 End If Next j If k > 0 Then Rows(i).Select Selection.EntireRow.Hidden = True End If Next i End Sub</pre>

 

[dellzy]

As I already mentioned this code is not too fast. It is better to use solution proposed by PeteRooneyHome's.

Check example under below link:

to dellzy:
Check this link. I change code to use Autofilter instead of just hiding the rows. it works for the months. In orange cell just input month number or name (not the date). the month column is F not (column 6) so if you need to change it for your code just change (Field:=6) to appropriate column number. Also if you would input month to different cell that A3 then code need to be adjusted.

https://www.dropbox.com/s/kpgw5t3bgspmr3l/HideRows2.xlsm

Hi skorpionz,
V sorry for the late delays as I just reached home fr work and this is considered sleeping time in my time zone here. hv no access to conputer right now so I'll try it tomorrow ya..btw, to answer some of ur questions (trying to recall as am using mobile phone rite now)
1.yes, both conditions must be met before execute the hiding. Eg. Chose month Jan at row A3, then dept at cell A4, then hide all that do not meet these selections.
2.i would add col month in A and push the rests on the follwing cols
3.cell A3
4.Staff name
5. Format would be "mmm"

Thank you in advance.

 

[skorpionkz]

2.i would add col month in A and push the rests on the follwing cols

Does this means, that Dept. name will be in Column I now?

5. Format would be "mmm"

Are you (or other users) just type "Jan" etc. or there is date formatted as "mmm"?

If my assumptions are correct check this file:
https://www.dropbox.com/s/a0het2803s3wzrc/HideRows - AutoFilter.xlsm

EDIT: In which columns the Staff Name is? If I would have this info then I can change code to cover correct range.

 

[dellzy]

Hi skorpionkz,

Does this means, that Dept. name will be in Column I now?

Very sorry for insufficient info. After additional of Month col in Col A, the table (row 5 downwards) should show like below:-


ColA ColB ColC ColD
Month Dept StaffName TotalLeave
Jan IT Roger 10
Jan Ops Catherine 5
Feb Sales Mariah 3
Mac IT Vicky 1
Apr IT Mike 3.5
Feb Sales Serene 0.5
Feb IT Roger 3
Jan IT Vicky 2
Feb IT Mike 1


So, there will be 2 validation lists, cell A3 on monthnames while A4 on Dept. For example, if user select "January" in A3 and "IT" in A4, so the output should be like below:-

ColA ColB ColC ColD
Month Dept StaffName TotalLeave
Jan IT Roger 10
Jan IT Vicky 2

https://www.dropbox.com/s/a0het2803s3wzrc/HideRows%20-%20AutoFilter.xlsm[/URL]

EDIT: In which columns the Staff Name is? If I would have this info then I can change code to cover correct range.

Value in Month col will be added based on selection in cell A3. The col is formatted as "mmm". Formula embedded in every row of ColA (after row 5 downwards) is as below:-

=IF(B6<>"",LEFT(A3,3),"")

After the info on all staff been entered for the selected month, then I will need to copy+paste this col and some other cols' values (i didn't mentioned here) that have some calculations as values only so that it won't change when user change the selection in A3 for other month and so forth. This part am still doing it manually (next phase to automate this). With this, I can have all the data I need and can create pivot table and graph to report to mgmt.

Hopefully above clarifies. I shall try your solutions in the links you gave and let know the results.

Thank you for your effort.

 

[skorpionkz]

Value in Month col will be added based on selection in cell A3. The col is formatted as "mmm". Formula embedded in every row of ColA (after row 5 downwards) is as below:-

=IF(B6<>"",LEFT(A3,3),"")

I can't get it. if you would use this formula for column A then the month from cell A3 would be applied to whole column and there is no point to sort by this, as it will include each row of your data. So each time you would have column B not empty the month will come up in column A exactly the same as in cell A3.

Another clarification I need. As per example you gave above it looks like you adding month in column A but also you move Dept info to column B. Is this correct?

Considering this both point the Month in column A will show up every time the department is in column B. Is there any row with data where you don't have department added?

 

[dellzy]

I can't get it. if you would use this formula for column A then the month from cell A3 would be applied to whole column and there is no point to sort by this, as it will include each row of your data. So each time you would have column B not empty the month will come up in column A exactly the same as in cell A3.

Another clarification I need. As per example you gave above it looks like you adding month in column A but also you move Dept info to column B. Is this correct?

Considering this both point the Month in column A will show up every time the department is in column B. Is there any row with data where you don't have department added?

Hi skorpionkz,
Very sorry. You are right. My bad. I checked through again and to put formula for the month column will totally ruin the objective to have a macro to do the required function. Now am attaching the sample of this for your better understanding. If the macro is sensitive based on datatype of the month col (either must be in format "mmm" in Custom date or can it allow text only or must it be in full date format like dd-mm-yyyy, etc), appreciate if you can advise me accordingly.

https://db.tt/RpqbcpsB

Hopefully the sample attached can give a better understanding.

Thank you very much for your effort.

 

[skorpionkz]

It is easier if you would just add the date in the month column (like 01/03/2014) and use custom format "mmm"

try this sample:

https://www.dropbox.com/s/a0het2803s3wzrc/HideRows - AutoFilter.xlsm

in Sheet module:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
Application.ScreenUpdating = False

Dim lrow As Long, lcol As Long, lMySearch As Long
Dim strMySearch As String, strMyMonth As String
Dim iMyMonth As Integer
Dim firstDateOfMonth As Long, lastDateOfMonth As Long

If Target.Address = "$A$3" Or Target.Address = "$A$4" Then
    If Not Cells(4, 1) = Empty And Not Cells(3, 1) = Empty Then
        Cells.EntireRow.Hidden = False
        
        Set Target = Range("A3,A4")
        strMySearch = Cells(4, 1)
        strMyMonth = Cells(3, 1)
        iMyMonth = month("01/" & strMyMonth & "/2014")
        
        firstDateOfMonth = DateSerial(Year(Date), iMyMonth, 1)
        lastDateOfMonth = DateSerial(Year(Date), iMyMonth + 1, 0)
        
        lrow = Cells(10000, 3).End(xlUp).Row
        lcol = Cells(5, 1).End(xlToRight).Columns
        
        With Range(Cells(5, 1), Cells(lrow, lcol))
            If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.Range.AutoFilter
            .AutoFilter Field:=2, Criteria1:=strMySearch
            .AutoFilter Field:=1, Criteria1:=">=" & firstDateOfMonth, Operator:=xlAnd, Criteria2:="<=" & lastDateOfMonth
        End With
    Else
        ActiveSheet.AutoFilterMode = False
    End If
ElseIf Target.Address = "$A$3:$A$4" Then
    ActiveSheet.AutoFilterMode = False
End If

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub