Поиск меток

Sub trvi()
    Dim Poisk As Range
 
    With Worksheets("Лист1")
        Set Poisk = .Range("A1:Z100").Find(What:="qw", LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        MsgBox Poisk.Row
 
.Rows(Poisk.Row).Hidden = True
 
            If Not Poisk Is Nothing Then
            sa = Poisk.Address
            Do
                Set Poisk = .FindNext(After:=Poisk)
                Loop While Not Poisk Is Nothing And Poisk.Address <> sa
            End If
    End With
End Sub

Sub ConditionalHide(ByVal rn As Range, ByVal searchFor As String)
   Dim r As Range
   Dim c As Collection
   Dim i As Long, j As Long
   Dim m() As Variant, v As Variant
   Set c = New Collection
   m = rn
   For i = 1 To rn.Rows.Count
      For j = 1 To rn.Columns.Count
         If Not IsError(m(i, j)) Then
            If m(i, j) = searchFor Then
               c.Add i
               Exit For
            End If
         End If
      Next j
   Next i
   If c.Count > 0 Then Set r = rn.Cells(c(1), 1)
   For Each v In c
      Set r = Union(r, rn.Cells(v, 1))
   Next v
   r.EntireRow.Hidden = True
End Sub
 
Sub ConditionalShow(ByVal rn As Range, ByVal searchFor As String)
   Dim r As Range
   Dim c As Collection
   Dim i As Long, j As Long
   Dim m() As Variant, v As Variant
   Set c = New Collection
   m = rn
   For i = 1 To rn.Rows.Count
      For j = 1 To rn.Columns.Count
         If Not IsError(m(i, j)) Then
            If m(i, j) = searchFor Then
               c.Add i
               Exit For
            End If
         End If
      Next j
   Next i
   If c.Count > 0 Then Set r = rn.Cells(c(1), 1)
   For Each v In c
      Set r = Union(r, rn.Cells(v, 1))
   Next v
   r.EntireRow.Hidden = False
End Sub

Sub Generate()
    
    Dim ws1 As Worksheet
    Set ws1 = Worksheets("Ëèñò1")
 
    Dim rn As Range
    Set rn = ws1.Range("A1:Z100")
    
Application.ScreenUpdating = False
 
    With ws1
         .Activate
         ' .Visible = xlSheetVisible
                ConditionalHide rn, "hide"
                ConditionalShow rn, "show"
         .PrintPreview
         ' .Visible = xlSheetHidden
    End With
 
 
Application.ScreenUpdating = True
End Sub
 
Sub ConditionalHide(ByVal rn As Range, ByVal searchFor As String)
   Dim r As Range
   Dim c As Collection
   Dim i As Long, j As Long
   Dim m() As Variant, v As Variant
   Set c = New Collection
   m = rn
   For i = 1 To rn.Rows.Count
      For j = 1 To rn.Columns.Count
         If Not IsError(m(i, j)) Then
            If m(i, j) = searchFor Then
               c.Add i
               Exit For
            End If
         End If
      Next j
   Next i
   If c.Count > 0 Then Set r = rn.Cells(c(1), 1)
   For Each v In c
      Set r = Union(r, rn.Cells(v, 1))
   Next v
   r.EntireRow.Hidden = True
End Sub
 
Sub ConditionalShow(ByVal rn As Range, ByVal searchFor As String)
   Dim r As Range
   Dim c As Collection
   Dim i As Long, j As Long
   Dim m() As Variant, v As Variant
   Set c = New Collection
   m = rn
   For i = 1 To rn.Rows.Count
      For j = 1 To rn.Columns.Count
         If Not IsError(m(i, j)) Then
            If m(i, j) = searchFor Then
               c.Add i
               Exit For
            End If
         End If
      Next j
   Next i
   If c.Count > 0 Then Set r = rn.Cells(c(1), 1)
   For Each v In c
      Set r = Union(r, rn.Cells(v, 1))
   Next v
   r.EntireRow.Hidden = False
End Sub

r.EntireRow.Hidden = False

Выходов тут может быть два: либо явное приведение к строковому типу с помощью функции CStr, либо проверка ячейки на содержание специального значения ошибки с помощью функции IsError. В коде ниже демонстрируется второй подход.

Sub Generate()
    Dim rn As Range
 
    With Worksheets("Ëèñò1")
        .Activate
        Set rn = .Range("A1:Z100")
         ' .Visible = xlSheetVisible
                Conditional rn, "hide", True
                Conditional rn, "show", False
         '.PrintPreview
         ' .Visible = xlSheetHidden
    End With
End Sub
 
Sub Conditional(ByVal rn As Range, ByVal searchFor As String, ByVal Hidden As Boolean)
   Dim r As Range, Cell As Range
   Dim i As Long, j As Long
   For Each Cell In rn
        If CStr(Cell.Value) = searchFor Then
            If r Is Nothing Then Set r = rn.Cells(Cell.Row, 1) Else Set r = Union(r, rn.Cells(Cell.Row, 1))
         End If
   Next
   If Not r Is Nothing Then r.EntireRow.Hidden = Hidden
End Sub