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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Forum statistics

Threads
1,214,661
Messages
6,120,793
Members
448,994
Latest member
rohitsomani

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