VBA - Do not insert row if columns are filtered

psykygy

New Member
Joined
Jun 15, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
I'm trying to only allow a new row to be inserted as long as not columns E & F have not been filtered.

VBA Code:
Sub addNewRow()
    
  ThisWorkbook.Worksheets("Overall Combination").Unprotect ("password")
    
    ' Do not insert a row before the first row.
    Const TopRow As Long = 10
    
    ' Get the active row number.
    Dim rowNum As Long
    rowNum = ActiveCell.Row
         
            
    If (rowNum > TopRow) And Not ActiveSheet.AutoFilter.Filters(5).On And Not ActiveSheet.AutoFilter.Filters(6).On Then

        Rows(rowNum).Insert       ' Insert a new row.
              
        Set CurRowR1 = Range("O" & ActiveCell.Row).Offset(-1)
        Set NewRowR1 = Range("O" & ActiveCell.Row)
        CurRowR1.Copy
        NewRowR1.PasteSpecial Paste:=xlPasteFormulas
        NewRowR1.PasteSpecial Paste:=xlPasteFormats
        Application.CutCopyMode = False
        
        Set CurRowR2 = Range("Q" & ActiveCell.Row & ":V" & ActiveCell.Row).Offset(-1)
        Set NewRowR2 = Range("Q" & ActiveCell.Row & ":V" & ActiveCell.Row)
        CurRowR2.Copy
        NewRowR2.PasteSpecial Paste:=xlPasteFormulas
        NewRowR2.PasteSpecial Paste:=xlPasteFormats
        Application.CutCopyMode = False
        
        Set CurRowR3 = Range("X" & ActiveCell.Row & ":AI" & ActiveCell.Row).Offset(-1)
        Set NewRowR3 = Range("X" & ActiveCell.Row & ":AI" & ActiveCell.Row)
        CurRowR3.Copy
        NewRowR3.PasteSpecial Paste:=xlPasteFormulas
        NewRowR3.PasteSpecial Paste:=xlPasteFormats
        Application.CutCopyMode = False
        
       Range("D" & ActiveCell.Row).Select
        
        ' === add a Check Box ===
        Dim oCB As CheckBox
        Dim c   As Range
        
        Set c = Cells(rowNum, 19)
        With c
            Set oCB = CheckBoxes.Add(.Left, .Top, .Width, .Height)
            oCB.Caption = vbNullString
            oCB.Display3DShading = True
            oCB.Width = 18.29
            oCB.Height = 14.89
        End With
        
    Else: MsgBox ("Cannot insert new row while either 'Pneu. Cabinet' or Valve Node' Columns are filtered")
        
    End If
    
  ThisWorkbook.Worksheets("Overall Combination").Protect ("password"), AllowFiltering:=True

    
End Sub

However I'm getting Run-time error '9': Subscript out of range for

VBA Code:
If (rowNum > TopRow) And Not ActiveSheet.AutoFilter.Filters(5).On And Not ActiveSheet.AutoFilter.Filters(6).On Then

I've tried to specify the sheet name rather than use ActiveSheet, but same error. What am I doing wrong?
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Forum statistics

Threads
1,224,823
Messages
6,181,184
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top