Help : select rows based on cell value then hide them

[jptaz]

Hello, I use this code to select rows based on the value in column U ("U1:U228").

Sub SelRows()
Dim ocell As Range
Dim rng As Range

For Each ocell In Range("U1:U228")

If ocell.Value = "#v" Then

If rng Is Nothing Then

Set rng = ocell.EntireRow
Else

Set rng = Union(rng, ocell.EntireRow)
End If
End If
Next

If Not rng Is Nothing Then rng.Select

Set rng = Nothing
Set ocell = Nothing
End Sub

Then I use this code to hide the selection

Sub hide1()
Selection.EntireRow.Hidden = True
End Sub

However I need to be on the right sheet (Rapport ini) for this to work
Would it be possible to edit these codes so they would work without being on the target sheet?


I've also tried this way but it is really too slow (few minutes)

Sub HRows()

     Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlManualCalculation
    
            
    BeginRow = 19
    EndRow = 221
    ChkCol = 21

    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value = "#v" Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        Else
            Cells(RowCnt, ChkCol).EntireRow.Hidden = False
        End If
    Next RowCnt
    
      Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlAutomaticCalculation
       
End Sub

Thanks for your time

 

[GWteB]

How about ...

Sub HideRows()
    Dim ocell As Range
    Dim rng As Range

    For Each ocell In Range("U1:U228")
        If ocell.Value = "#v" Then
            If rng Is Nothing Then
                Set rng = ocell
            Else
                Set rng = Union(rng, ocell)
            End If
        End If
    Next

    If Not rng Is Nothing Then rng.EntireRow.Hidden = True

    Set rng = Nothing
    Set ocell = Nothing
End Sub

 

[Fluff]

How about

Sub SelRows()
Dim ocell As Range
Dim rng As Range

For Each ocell In Sheets("Rapport ini").Range("U1:U228")

If ocell.Value = "#v" Then

If rng Is Nothing Then

Set rng = ocell.EntireRow
Else

Set rng = Union(rng, ocell.EntireRow)
End If
End If
Next

If Not rng Is Nothing Then rng.EntireRow.Hidden = True

Set rng = Nothing
Set ocell = Nothing
End Sub

 

[JEC]

Here you go, working for sheet 1

Sub jec()
 Set c00 = Sheets(1).Cells(10000, 1)
 For Each it In Sheets(1).Range("U1:U228")
   If it.Value = "#v" Then Set c00 = Union(c00, it)
 Next
 If Not c00 Is Nothing Then c00.EntireRow.Hidden = True
End Sub

 

[jptaz]

How about

Sub SelRows()
Dim ocell As Range
Dim rng As Range

For Each ocell In Sheets("Rapport ini").Range("U1:U228")

If ocell.Value = "#v" Then

If rng Is Nothing Then

Set rng = ocell.EntireRow
Else

Set rng = Union(rng, ocell.EntireRow)
End If
End If
Next

If Not rng Is Nothing Then rng.EntireRow.Hidden = True

Set rng = Nothing
Set ocell = Nothing
End Sub

Hello,

thank you very much, your solution works great! Thanks to you too GWteB!!

Have a nice day!

​

 

[Fluff]

Glad we could help & thanks for the feedback.