Excel Made Easy

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.

excel vba clear cell after macro runs for this cell

To do it in Excel, here is the answer:

  1. Option Explicit
  2. Private Sub WorkSheet_Change(ByVal Target As Range)
  3. Dim KeyCells As Range
  4. Dim sName As String
  5. ''
  6. Set KeyCells = ActiveSheet.Range("$AV$2")
  7. If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
  8. ''
  9. sName = ActiveSheet.Range("$AV$2").Value
  10. ''
  11. 'Clear existing data in Destination.
  12. ActiveSheet.Range("AT4:AV14").Clear
  13. ''
  14. 'Filter rows based on Name which is Field 2 (Col AQ).
  15. ActiveSheet.Range("AP4:AR4").AutoFilter
  16. ActiveSheet.Range("AP4:AR14").AutoFilter Field:=2, Criteria1:=sName
  17. ''
  18. 'Copy filtered table and paste it in Destination cell.
  19. ActiveSheet.Range("AP4:AR14").SpecialCells(xlCellTypeVisible).Copy
  20. ActiveSheet.Range("AT4").PasteSpecial Paste:=xlPasteAll
  21. Application.CutCopyMode = False
  22. ''
  23. 'Remove filter that was applied.
  24. ActiveSheet.AutoFilterMode = False
  25. ''
  26. 'Disable Events to avoid clearing of Target cell contents from activating this Worksheet_Change routine again.
  27. Application.EnableEvents = False
  28. ''
  29. Target.ClearContents
  30. ''
  31. 'Enable Events
  32. Application.EnableEvents = True
  33. ''
  34. End If
  35. 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.

 

You can find similar Excel Questions and Answer hereunder

1) How can I identify the cells that influence a particular cell to help with debugging of my spreadSheet or understand a spreadSheet that I inherited?

2) How do I know which cells on the worksheet contain Conditional Formatting?

3) How can I set FreezePanes in a certain range using VBA?

4) How do I restrict user entry in ActiveX Text Box to just numeric values?

5) Converting numbers stored as text to numbers via macro in Excel

6) How to enable or disable macros execution or loading in Excel

7) How to do webscrapping in VBA. Here some basics

8) I frequently use a Macro - is there a way to quickly access the Macro in the Excel Ribbon?

9) How can I set Page orientation, Zoom % , Title Rows and footer using VBA?

10) Want to use Microsoft Outlook in Excel. Here some basic explanation to get you started

 

Here the previous and next chapter

Excel Made Easy was founded in 2002. Excel Made Easy is a service to people looking to learn Microsoft Excel or any other spreadsheet SW on the market.

We endeavor to provide the best service and support and to respond quickly to your questions.

Excel Made Easy is a proud sponsor of Dismantle-It.com the site that shows you the belly of your daily objects, Poupounette.com the site of cars loving cat and PowerPointMadeEasy.com the world top reference for PowerPoint Presentations questions and Pet Cemetery Online a site dedicated to your loving Pets.