Excel vba hide rows until value

Question

I want to hide rows 7 through X in Excel, where X is the row 6 rows above the first instance of a value in column A.

Google seems to come up with things that look WAY WAY too complicated for my purposes.

What I have so far is a basic:

'Select Rows 7 through 3 rows before the first instance of any text in column A, then hides them
Rows("7:62").Select
' have to update the 62 above to look for text and hide based on that
Selection.Rows.Group
Selection.EntireRow.Hidden = True

within my Sub. I just need to figure out how to change the 62 to be a variable cell that is usually around the 60's, but is not always the exact value of 62.

What are you googling? Because the answer should not be `WAY WAY too complicated`. — findwindow, Jun 03 '16 at 16:51
I'm thinking you could use the Find function, and then assign the value of the cell that you find using the function the row that it is in. At that point you just do that row minus 6 and then replace the "62" with that variable. — timthebomb, Jun 03 '16 at 16:53
My VBA in Excel is rusty, but I would probably do something to find the cell with a value in it, use the row number as a variable, subtract 6 from it, and then use that value in place of the 62. (I don't even know if what I said made sense to you, and I'm fairly sure it's not the best way). — phroureo, Jun 03 '16 at 16:53
1) Way too complicated for me, I should specify (I[m quite new to VBA and macros) 2) I don't know how to use the "find function" in the context of the macro. — E. Isaac, Jun 03 '16 at 17:10

score 1 · Answer 1 · answered Jun 03 '16 at 17:06

1

Try to avoid using Select when possible.

strValueToFind = "What ever the value you are trying to find in column A is"
With Sheets("Sheet1")
    'Find the row containing the value
    intFoundRow = .Range("A:A").Find(What:= strValueToFind, _
        LookIn:=xlValues, _
        LookAt:=xlWhole).Row
    'Now hide Rows 7 through 6 above the row we found
    .Rows("7:" & intFoundRow - 6).Hidden = True
End With

Hopefully that's not too complicated. :)

answered Jun 03 '16 at 17:06

Tim

2,701
3
26
47

Thanks! that works. Any way I can modify it to find the first instance of ANY text in column A? It's usually the same value but occasionally is annoyingly slightly different. – E. Isaac Jun 03 '16 at 17:18
Yes, so many ways to do this. Here's two: `strValueToFind = Sheets("Sheet1").Range("A1").Value` will find whatever you type into cell A1. You could also create a userform with a TextBox control, and the it would be `strValueToFind = UserForm1.TextBox1.Text` – Tim Jun 03 '16 at 17:21
Is there a way to just have it find ANY value in column A and set this as the anchor-point for the bottom (or anchor point for x number of cells before the bottom) of the hidden range? – E. Isaac Jun 03 '16 at 17:27
`ANY value in column A` means that the first row will *always* be the first match (even a blank cell is a value). See [this link](http://stackoverflow.com/questions/14957994/select-first-empty-cell-in-column-f-starting-from-row-1-without-using-offset) if you mean something like find the first empty cell in column A. To find the first non-empty cell then simply reverse the logic. Eg `If currentRowValue <> "" Then intFoundRow = CurrentRow` – Tim Jun 03 '16 at 17:40
Yes, I meant I am trying to find the first non-blank cell in column A and set the end point of the hidden rows off of this value. – E. Isaac Jun 03 '16 at 18:01

score 1 · Accepted Answer · answered Jun 03 '16 at 17:14

1

Your narrative says 'row 6 rows above' and your code says '3 rows before'. I usually believe the code. This is important due to the maths involved and ensuring that you are not trying to hide rows above row 7.

Dim rw As Variant
rw = Application.Match(Chr(42), Range("A8:A1048576"), 0)
If Not IsError(rw) Then
    rw = Application.Max(rw + 4, 7)
    Range("A7:A" & rw).EntireRow.Hidden = True
End If

answered Jun 03 '16 at 17:14

thanks. and good pickup. Im sort of indecisive on what i want to show as the header. But I think I can tweak that within the context of the code. – E. Isaac Jun 03 '16 at 17:20

score 0 · Answer 3 · answered Jun 03 '16 at 17:11

Say we start with:

and we want to hide all rows 7 through 6 rows above the first occurrence of "happiness". Running this:

Sub FindAndHide()
    Dim rng As Range

    Set rng = Range("A:A")
    Range(Cells(7, 1), rng.Find(What:="happiness", after:=rng(7)).Offset(-6, 0)).EntireRow.Hidden = True
End Sub

will produce:

Excel vba hide rows until value

3 Answers3