PDA

View Full Version : Hide Rows to variable specific criteria



dgt
11-04-2014, 12:57 PM
Hi all

Been struggling with various bits of code that I found online to try and resolve this problem but without success.

In the attached sample worksheet, I want to automatically hide rows prior to the date shown in C5 as they will always be zero. So in this instance I would like to hide all rows highlighted in yellow. I have been trying out code to work on the basis that all rows are hidden before the entry in E138 based upon it matching the figure in E5.

However, when a new customer is inputted in C2 the figures and starting point will change to a different position (date) depending upon the date shown in C5 which could result in the filled range starting anywhere from A9 to A156 which also expands on a weekly basis.

The grey area at the bottom contains various formula but there is always 2 empty rows between the final row of data and these calculations. I use the me.calculate to update the formulas as new input is entered in the top data cells above row 9.

Hopefully this can be resolved quite simply.

TIA ...David
:hi:

SamT
11-04-2014, 02:54 PM
Range(Range("C9"), Range( Range("C9:C" & Rows.Count).Find(VLookUp(blah,blah,blah, C5))).EntireRow.Hide

Use VLookup because it returns the next largest value. It might leave one earlier row showing

Verify Paren count above.

Aussiebear
11-04-2014, 04:42 PM
Verify Paren count above.

No worry Sam, I found him loitering on the keyboard!

dgt
11-04-2014, 05:17 PM
Hi Sam

Thanks for the code but my knowledge of VBA is very rudimentary. I have tried to create something with it but all I get is a Runtime Error #104 relating to the VLookUP part of the code.


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Range(Range("C9"), Range(Range("C9:C" & Rows.count). _
Find(WorksheetFunction.VLookup("C5", "A9:E200", 1, False)))).EntireRow.Hide

Me.Calculate
End Sub

Could you help me out further with the full code.

TIA ...David

SamT
11-04-2014, 09:52 PM
I expanded it to make it easier to understand and because you are using two different Date formats. Now it will work with any date format in Cell("C5")


Option Explicit



Private Sub Test() 'Worksheet_Change(ByVal Target As Range)

Dim FirstDate As String
Dim LastHiddenRow As Long

'If Not Target is Range("C5") Then Exit Sub 'Uncomment to restrict triggering Sub


'Different Formats in Column A and Cell C5
FirstDate = Format(WorksheetFunction.VLookup(Range("C5"), Range("A:A"), 1, True), "m/d/yyyy")

LastHiddenRow = Range("A:A").Find(FirstDate).Row - 1

Rows("9:" & LastHiddenRow).Hidden = True

End Sub



Sub UnHideAll() 'Selection_Change(Target As Range)
'Unhides all Rows
'If Not Intersect(Target, Range("A7:F7") Is Nothing Then Exit Sub 'Uncomment As above

Rows("1:" & Rows.Count).Hidden = False

End Sub