I'm trying to only allow a new row to be inserted as long as not columns E & F have not been filtered.
However I'm getting Run-time error '9': Subscript out of range for
I've tried to specify the sheet name rather than use ActiveSheet, but same error. What am I doing wrong?
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?