Vba clear cell after macro runs for this cell in Excel
For example, I want to filter records pertaining to the person name specified in cell AV2. Whenever name in AV2 changes, I want to apply filter based on name selected and then copy the filtered data and paste it in cell AT4.
Then I want to clear the name without affecting the filtered data that has been copied over into cell AT4.
To do it in Excel, here is the answer:
- Option Explicit
- Private Sub WorkSheet_Change(ByVal Target As Range)
- Dim KeyCells As Range
- Dim sName As String
- ''
- Set KeyCells = ActiveSheet.Range("$AV$2")
- If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
- ''
- sName = ActiveSheet.Range("$AV$2").Value
- ''
- 'Clear existing data in Destination.
- ActiveSheet.Range("AT4:AV14").Clear
- ''
- 'Filter rows based on Name which is Field 2 (Col AQ).
- ActiveSheet.Range("AP4:AR4").AutoFilter
- ActiveSheet.Range("AP4:AR14").AutoFilter Field:=2, Criteria1:=sName
- ''
- 'Copy filtered table and paste it in Destination cell.
- ActiveSheet.Range("AP4:AR14").SpecialCells(xlCellTypeVisible).Copy
- ActiveSheet.Range("AT4").PasteSpecial Paste:=xlPasteAll
- Application.CutCopyMode = False
- ''
- 'Remove filter that was applied.
- ActiveSheet.AutoFilterMode = False
- ''
- 'Disable Events to avoid clearing of Target cell contents from activating this Worksheet_Change routine again.
- Application.EnableEvents = False
- ''
- Target.ClearContents
- ''
- 'Enable Events
- Application.EnableEvents = True
- ''
- End If
- End Sub
Description:
a) The above code has to be included in the module of the "Sheet" in which the input Table resides.
b) Line 2 - Whenever there is change in any cell in the sheet, this routine is executed.
c) Line 7 - Check if the change in WorkSheet pertains to cell of interest (AV2 in this case). If yes, execute code to filter, copy filtered data, paste it in destination and remove filter (Lines 7-23).
d) Line 27 prevents the code from entering into infinite loop by preventing "Worksheet_Change" routine from being activated when Target cell content is cleared.