Hi to All,
I am new to VBA code writing and having problem in executing a code. I have a workbook with 20 sheets. The first sheet named "Summary" has a cell reference B5 wherein user will have to input a 4 digit code. Once entered a macro will run on all other worksheets deleting rows except the header without the 4 digit code. In all worksheets the column to search the 4 digit code is column A. I am using the following code, which I have got after doing google search;
Sub DeleteRows()
'Updateby20140314
Dim rng As Range
Dim InputRng As Range
Dim DeleteRng As Range
Dim DeleteStr As String
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = ActiveSheet.Range("A3:A200000")
DeleteStr = Worksheets("Summary").Range("B5")
For Each rng In InputRng
If rng.Value <> DeleteStr Then
If DeleteRng Is Nothing Then
Set DeleteRng = rng
Else
Set DeleteRng = Application.Union(DeleteRng, rng)
End If
End If
Next
DeleteRng.EntireRow.Delete
End Sub
But I am unable to automate the workbook so that the macro will trigger whenever any data is feed in "Summary" Sheet Cell "B5".
If anyone can help me it will be really great.
I am new to VBA code writing and having problem in executing a code. I have a workbook with 20 sheets. The first sheet named "Summary" has a cell reference B5 wherein user will have to input a 4 digit code. Once entered a macro will run on all other worksheets deleting rows except the header without the 4 digit code. In all worksheets the column to search the 4 digit code is column A. I am using the following code, which I have got after doing google search;
Sub DeleteRows()
'Updateby20140314
Dim rng As Range
Dim InputRng As Range
Dim DeleteRng As Range
Dim DeleteStr As String
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = ActiveSheet.Range("A3:A200000")
DeleteStr = Worksheets("Summary").Range("B5")
For Each rng In InputRng
If rng.Value <> DeleteStr Then
If DeleteRng Is Nothing Then
Set DeleteRng = rng
Else
Set DeleteRng = Application.Union(DeleteRng, rng)
End If
End If
Next
DeleteRng.EntireRow.Delete
End Sub
But I am unable to automate the workbook so that the macro will trigger whenever any data is feed in "Summary" Sheet Cell "B5".
If anyone can help me it will be really great.