Delete group of rows

Question

I want to delete all the rows from sheet3 where sheet3 Column 'N' value is not between sheet "Main" cell B12 and D12 value. So I tried with following code

With ActiveSheet
     Lvl = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
     .AutoFilterMode = False
     .Range("N1").AutoFilter Field:=14, Criteria1:="<" & _
      Format(Sheets("Main").Cells(12, 2), "DD-MM-yyyy")
     .Offset(1).SpecialCells(12).EntireRow.Delete
     .AutoFilterMode = False
end with 

But it gives an Error on Offset line Object doesn't support this property or method

Answer 1

Couple things here. A good practice is to avoid ActiveSheet. Define a worksheet object and set it to that sheet. This makes it MUCH easier to reference later in your code.

Dim wsMain as Worksheet
Set wsMain = thisworkbook.sheets("Main")

Then, define a range within that sheet that you'd autofilter and delete the rows matching your criteria. Edit: you will want to reverse the filter to show the cells meeting your criteria and then deleting that selection.

wsMain.AutoFilterMode = false
'format wsMain however you'd like in here
Dim lRow, lCol as Integer
    lRow = wsMain.Cells(Rows.Count, 1).End(xlUp).Row
    lCol = wsMain.Cells(1, Columns.Count).End(xlToLeft).Column
Dim rngTar as Range
    Set rngTar = wsMain.Range(wsMain.Cells(1, 1), wsMain.Cells(lRow, lCol))
With rngTar  'filter to show everything that matches the values in the range you specified
     .AutoFilter Field:=14, Criteria1:=">=wsMain.Range("B12:D12")
     .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
wsMain.AutoFilterMode = false

Edit: if you'd like to have multiple filters, simply add another .Autofilter line!

With rngTar
     .AutoFilter Field:=14, Criteria1:=">=wsMain.Range("B12:D12")
     .AutoFilter Field:=17, Criteria1:="Inactive"
     .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

Hope this helps!

Answer 2

There are a couple of things wrong and a few that can be improved in that code snippet.

a) The With ... End With statement

With ActiveSheet
     ...
End With

You should know what worksheet you are on. The above might be useful if you plan to perform it on one worksheet then activate another and perform it again but you can loop through worksheets as well.

b) Redundantly referencing the worksheet

     Lvl = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

You have defined the worksheet to be processed in your With ... End With statement. There is no need to keep referring to it so long as you prefix the Range and Range.Cells objects with a . (DOT)¹.

c) Starting with a new Range.AutoFilter Method

     .AutoFilterMode = False
     ...
     .AutoFilterMode = False

Good code here. I personally check to see if there is an autofilter (e.g. if .AutoFilterMode then .AutoFilterMode = False ) before turning it off but there is nothing wrong with a straight overwrite regardless of the original value. Always good to turn it off before exiting as well unless it is needed for the user.

It may be worthwhile to note that if used on the cells in the .CurrentRegion property or .UsedRange property the Range.Parent property needs to be added as .AutoFilterMode is a property of a Worksheet Object, not a Range object. (see example below)

d) What column to set the Range.AutoFilter Method on?

     .Range("N1").AutoFilter Field:=14, Criteria1:="<" & _
          Format(Sheets("Main").Cells(12, 2), "DD-MM-yyyy")

Yes column N is the 14^th column relative to the worksheet but if you are going to set the autofilter like this then it may be the one and only column relative to column N. .Range("N1") should be .Range("N:N") and this should be Field:=1 to maintain reliability because column N is the first and only column when you are only looking at column N.

e) Setting a Date-Type criteria in the Range.AutoFilter Method

Your string date is not going to be interpreted as a date but as a string. Strings cannot be reliably used with < or > operators in criteria. Your best hope for success is to use the raw underlying Range.Value2 property from the date on the Main worksheet with the less than comparison operator. (see example below)

f) Trying to move (aka .Offset) off the worksheet

     .Offset(1).SpecialCells(12).EntireRow.Delete

You are working with the entire worksheet, not the Range.CurrentRegion property or Worksheet.UsedRange property. You cannot take 1048576 rows × 16384 columns and .Offset it one cell in any direction without resizing it first because that will try to push one row or one column off the worksheet.

In theory, you could Range.Resize the block of cells you are working with as long as you .Resize before .Offset.

'this works
.Resize(.Rows.Count-1, .Columns.Count).Offset(1).SpecialCells(12).EntireRow.Delete
this does not work
.Offset(1).Resize(.Rows.Count-1, .Columns.Count).SpecialCells(12).EntireRow.Delete

Additionally, the Range.Offset property is a property of a Range object, not a Worksheet Object and there are better ways of dealing with a limited area of cells. (see CurrentRegion below)

g) The Range.Delete method and Range.SpecialCells method

Using .SpecialCells with the xlCellTypeVisible option is not strictly necessary when deleting rows and only adds more processing.

Simply step off the header row as you've done with the .Offset command (optionally resizing to one row less) and .Delete the .EntireRow.

I prefer to make sure that there are cells to delete with the worksheet's native SUBTOTAL function. There used to be a bug where when there were no visible cells and all cells to the end of the worksheet were included but I believe that has been fixed with service packs. Regardless, making a non-destructive test for visible cells with SUBTOTAL is good coding practise.

z) Using the Range.CurrentRegion property.

^{My examples for the .CurrentRegion will deal with a block of cells radiating out from A1 until it meets a completely blank column in one direction and a completely blank row in the other.}
_{While this is not always the case, it is by far the most common. Adjustments would have to be made if this data pattern does not match your own.}

The Range.CurrentRegion property in a With ... End With statement will serve to focus your code on the cells radiating out from a single cell. In many cases it is like the Worksheet.UsedRange property but is defined by values on the worksheet only, not the xlCellTypeLastCell property. In fact, your use of Range("N1").AutoFilter uses the .CurrentRegion centered with N1 as the origin point to define the .AutoFilter's range.

When used in a With ... End With statement, all references within the With ... End With block becomes relative to the .CurrentRegion; e.g. The Worksheet.Rows becomes the Range.Rows property.

The .CurrentRegion of any cell can be examined by selecting any cell and tapping Ctrl+A once. It extends (radiates out) in all directions; not just down and right unless the origin cell is A1.

Your code modified

Dim lvl As Long
With Worksheets("Sheet2")
    'work with the block of cells radiating out from A1
    With .Cells(1, 1).CurrentRegion
        'if the parent worksheet already has an AutoFiltyer, remove it
        If .Parent.AutoFilterMode Then .Parent.AutoFilterMode = False
        lvl = .Rows.Count   '<~~ not sure what LVL is actually used for but this is all that is needed
        With .Range("N:N")
            'filter on the raw numerical .value2 of the date
            .AutoFilter Field:=1, _
                        Criteria1:="<" & Worksheets("Main").Cells(12, 2).Value2, _
                        Operator:=xlFilterValues
        End With
        'step off the header row
        With .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0)
            'are there visible cells
            If CBool(Application.Subtotal(103, .Cells)) Then
                'don't need .SpecialCells(xlCellTypeVisible) here
                .EntireRow.Delete
            End If
        End With
    End With
    'remove the AutoFilter
    If .AutoFilterMode Then .AutoFilterMode = False
End With

     
                  ^{Sample data before procedure                                  Sample data after procedure}

While you and I do not do everything the same way I hope this helps point you in the right direction.

---

¹ _{There is also the Rows.Count which has no prefix character. In theory you could get in trouble if the code was being run on an XLS (with 65536 rows) and the ActiveSheet property was held by an XLSX (with 1048576 rows). I do not personally subscribe to this practise all of the time for other reasons (see this).}