VBA question -ranges

[Hiport]

Is it possible to put two ranges in one line of code?

  With shtTest
    Intersect(.UsedRange, .Range("A8:H" & Rows.Count)).ClearContents
    Intersect(.UsedRange, .Range("N8:S" & Rows.Count)).ClearContents
  End With

 

[pgc01]

Hi Hiport

Try:

With shtTest
    Intersect(.UsedRange, Application.Union(.Range("A8:H" & Rows.Count), .Range("N8:S" & Rows.Count))).ClearContents
End With

 

[Hiport]

Thankyou

Hi Hiport

Try:

With shtTest
    Intersect(.UsedRange, Application.Union(.Range("A8:H" & Rows.Count), .Range("N8:S" & Rows.Count))).ClearContents
End With

 

[Hiport]

One more question, how can i add a msgbox which will return "No items to provision" Exit Sub if the criteria "provisioned" is not found?

Public Sub copyVisibleCols()
 Dim shtTest1, shtTestAs Worksheet
 
 Set shtTest1 = Sheets("Test1")
 Set shtTest= Sheets("Test")
 
    With shtTest
     Intersect(.UsedRange, Application.Union(.Range("A8:H" & Rows.Count), .Range("N8:S" & Rows.Count))).ClearContents
    End With

    With shtTest
        If .FilterMode Then .ShowAllData
        With .Range("A5").CurrentRegion
        .AutoFilter Field:=1, Criteria1:="Provisioned"
        On Error Resume Next
        .Offset(1, 1).Resize(.Rows.Count - 1, 1).Resize(, 3).SpecialCells(xlCellTypeVisible).Copy
        shtTest1.Range("A8").PasteSpecial xlPasteValues
        
        .Offset(1, 5).Resize(.Rows.Count - 1, 1).Resize(, 1).SpecialCells(xlCellTypeVisible).Copy
        shtTest1.Range("D8").PasteSpecial xlPasteValues
        
        .Offset(1, 12).Resize(.Rows.Count - 1, 1).Resize(, 1).SpecialCells(xlCellTypeVisible).Copy
        shtTest1.Range("E8").PasteSpecial xlPasteValues
 
        End With
      If .FilterMode Then .ShowAllData
   End With
End Sub

Hi Hiport

Try:

With shtTest
    Intersect(.UsedRange, Application.Union(.Range("A8:H" & Rows.Count), .Range("N8:S" & Rows.Count))).ClearContents
End With

 

[pgc01]

Hi

One way is to look for the criterion in the first column of the range before applying the filter.

...
        With .Range("A5").CurrentRegion
            If .Columns(1).Find(What:="Provisioned", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False) Is Nothing Then
                MsgBox "bye"
                Exit Sub
            End If
...

Another way would be to apply the filter and then check if there are visible rows.