let's say in the same worksheet You have Table1 and Table2 and you want to delete a row from table 1 dynamically by just selecting a cell inside the table range that's how my client wants to remove a row.
I prepared the workbook to show the demo to delete a row of specific table range by selecting the cell inside of it.
Here is the Code :
Private Sub CommandButton1_Click()
If ActiveCell.Value = vbNullString Then Exit Sub
Const RestrictedRow As Byte = 1
Const FirstColofTable1 As Long = 1
Const LastColofTable1 As Long = 5
Const FirstColofTable2 As Long = 7
Const LastColofTable2 As Long = 11
Const Table1LeftIndex As String = "A"
Const Table1RightIndex As String = "E"
Const Table2LeftIndex As String = "G"
Const Table2RightIndex As String = "K"
Dim ActiveCellRow As Long
Dim ActiveColumn As Long
ActiveCellRow = VBA.Val(ActiveCell.Row)
ActiveColumn = VBA.Val(ActiveCell.Column)
If ActiveCellRow = RestrictedRow Then Exit Sub
With Sheets("Sheet2")
If FirstColofTable1 <= ActiveColumn And ActiveColumn <= LastColofTable1 Then
Sheets("Sheet2").Range(Table1LeftIndex & ActiveCellRow & ":" & Table1RightIndex & ActiveCellRow).Delete shift:=xlUp
ElseIf FirstColofTable2 <= ActiveColumn And ActiveColumn <= LastColofTable2 Then
Sheets("Sheet2").Range(Table2LeftIndex & ActiveCellRow & ":" & Table2RightIndex & ActiveCellRow).Delete shift:=xlUp
End If
End With
MsgBox "Deleted"
End Sub
No comments:
Post a Comment