Hiding range of rows

[jimayers]

Hello - I am having trouble with finding a value in cells in "A" (= "ethnic balance") and hiding rows in a certain range around the found value. Here is what I have started but cant get to work:
Sub HideRows()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim hRow, uRow, bRow As Range
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow
If Cells(i, "A") = "ETHNIC BALANCE" Then
hRow = Range(i, "A").Row
bRow = Range(i + 3, "A").Row
uRow = Range(i - 8, "A").Row
Range.Row("uRow:hRow").Hide
Range.Row("hRow:dRow").Hide
End If

Next
Application.ScreenUpdating = False
End Sub

Thanks for your help and if possible point out my errors - Jim A

 

[lrobbo314]

Try

Rows(uRow & ":" & hRow).Hidden = True

 

[HalfAce]

What, exactly is it you're trying to do? From reading your code it appears you want to:
1. Loop down through column A looking for the value 'ETHNIC BALANCE'.
2. When found (say in cell A10) you want to - what? Hide rows 2 through 13?

If that's correct then you can try something like this:

Sub HideRows()
 Application.ScreenUpdating = False
 Dim i As Long
 Dim Lastrow As Long
 Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
 For i = 1 To Lastrow
    If Cells(i, "A") = "ETHNIC BALANCE" Then Rows(i - 8 & ":" & i + 3).Hidden = True
 Next
 Application.ScreenUpdating = False
 End Sub

If that's not correct, then I'm afraid you've got some more explaining to do...

Hope it helps.

 

[HalfAce]

Oh, and I didn't notice before... you should change that last line to Application.ScreenUpdating = True

 

[jimayers]

What, exactly is it you're trying to do? From reading your code it appears you want to:
1. Loop down through column A looking for the value 'ETHNIC BALANCE'.
2. When found (say in cell A10) you want to - what? Hide rows 2 through 13?

If that's correct then you can try something like this:

Sub HideRows()
 Application.ScreenUpdating = False
 Dim i As Long
 Dim Lastrow As Long
 Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
 For i = 1 To Lastrow
    If Cells(i, "A") = "ETHNIC BALANCE" Then Rows(i - 8 & ":" & i + 3).Hidden = True
 Next
 Application.ScreenUpdating = False
 End Sub

If that's not correct, then I'm afraid you've got some more explaining to do...

Hope it helps.

YES...except when value "Ethnic Balance" is found hide the 8 rows above it and 3 rows below. Then move on to find the value= "Ethnic Balance" again, repeating operation.
Thanks - Jim A

 

[HalfAce]

Are you wanting to hide the row(s) containing 'Ethnic Balance' as well? (What it does now.) Or do you only want to hide the 8 rows above and 3 rows below it, leaving "Ethnic Balance' showing?

Just to be clear, right now it will do what you've described, including the row(s) containing 'Ethnic Balance'. We can leave those showing easily enough if that's what you want.

 

[jimayers]

Yes, hide everything including the row that contains ethnic balance.
I just cant get it to work.
Thanks - JA

 

[jimayers]

Oh...I didn't see your suggested code. I did not try it. But I will need to loop it for all cells to the "lastcell"
But what about mine makes it so it doesn't work?
Thanks - JA

 

[jimayers]

It gives me an error( Method "Range" of object '_Global fail') at:
bRow = Range(i + 3, "A").Row

 

[jimayers]

OK...I got your to work by adding "i" after next...Next i

Thanks for your help and patience!!