Snasui Excel Forum

Code: Select all

Sub test()
    ' Dim i As Long
    Dim r As Range
    Dim rall As Range
    
    ' LR = Sheets("Sheet1").Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    Set rall = ActiveSheet.Range("V5", ActiveSheet.Range("V" & Rows.Count).End(xlUp))
    ' For i = LR To 2 Step -1
    For Each r In rall
        If r = ActiveSheet.Range("B2") Then
            r = ""
        End If
    Next r
    rall.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

Code: Select all

'... other code
    Dim rAll As Range
    Set rAll = Sheets("Sheet1").Range("A4", Sheets("Sheet1").Range("V" & Rows.Count).End(xlUp))
       ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!" & rAll.Address).CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable7", DefaultVersion:=xlPivotTableVersion10
'... other code