The macro itself is a fairly simple one-line command : This is another handy macro, which filters the current column based on the value of the active cell, except that the filter is applied as “show records NOT equal to the value of the active cell” 2: Filter on value NOT equal to ActiveCell value MsgBox "No cells found with this cell's contents"Īs you will notice, the macro checks whether the selection is 1 cell or multiple cells, and accordingly executes the Cells.Find command. MatchCase:=False, SearchFormat:=False).Activate
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ (What:=ActiveCell.Value, After:=ActiveCell, LookIn:=xlValues, _
This is a very useful macro which helps to search for the value in the ActiveCell within the selected range or the whole worksheet (if only ActiveCell is selected). Hopefully I will get a chance to post some more if this post is found to be good enough ?ġ: Find the value of ActiveCell within selection, or in the whole sheet This is the first bunch of macros which I use most frequently. And you can easily port it to any other computers that you use – or even share it with your friends and allied spooks. Like nuclear war, It’s a one-time exercise.
You can read more about how to set up a Personal Macro Workbook, in this excellent tutorial on Ron de Bruin’s website. This post is about some of the stuff that I have put in my Personal Macro Workbook over the years. There are many ways one can do this in Excel, but among the more effective and scalable ones, is storing commonly used macros in your Personal Macro Workbook. In fact, this is a topic that I think about in everything to do with computers. One of my favorite topics in Excel is – “How can I make my day-to-day tasks in Excel easier and faster ?”. I have been using spreadsheets since 1990, and Excel since 1995 – which sort of makes me a veteran in this sphere of business applications ? Hello, this is my first guest post on (or any Excel website for that matter), and I will try to keep it simple, but useful for our readers. So without further ado, please put your hands together and give a warm Chandoo welcome to secret agent KV. As discussed then, I ‘volunteered’ KV under pain of exposure to empty the contents of his secret satchel onto the virtual table, so that we can rummage through it. All you need to know is how to Google, Cut, and Paste.
See the screen shots below.ĭownload EventSeq.zip and let me know what you think.Yesterday, I talked about how you don’t have to know how to code in order to highly leverage VBA. This allowed me to stick a note in the listbox to deliniate when I did something. I ended up writing everything in the listbox to a CSV file when the file closes.Īs long as I was in there, I added code for all the events through 2003. They all get logged, but the userform disappears so fast, you can’t see them. One problem I encountered using this method is logging the events that happen when you close the workbook. Since Chip did all the work, all I had to do was make a procedure to fill the listbox, then do a find and replace. Instead of message boxes, I log the events in a listbox. As I was using that file, I got tired of clearing all those darn message boxes, so I tweaked things a little. Someone sent me an email asking which event fires when you add a shape to a worksheet. I use it at least a couple of time per year, sometimes to see the order events, but more often to see which event fires when I do something. It has a message box in every event so you can see what events get fired when and in what order.
Over on Chip Pearson’s download page, there’s a file called EventSeq.xls.