Run a Macro when data entered in a cell

In Excel worksheet do the following :-

1. Click on the Developer tab.
2. Click on the Visual Basic icon.
3. On the left pane window, double click the sheet where you need your code to run.
4. Now, at the top of the code window you will see... (General) with a drop down, and (Declarations) with a drop down.
5. Click the drop down by (General) and select Worksheet.
6. Now in the code window you will see.... Private Sub Worksheet_SelectionChange(ByVal Target As Range)
7. Remove the word "Selection". You want to remove "Selection" because that means when you click on a cell in the worksheet something will happen. You do not want
8. that, you want to enter a value in D10. It should now read...Private Sub Worksheet_Change(ByVal Target As Range)
9. Now this is where you want the code...

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$D$10" Then 

Call MyMacro 

End If 

End Sub 

Delete Blank Rows

If you have a long list of data that contains blank rows that you want to delete, you have several options.

• You could select each row, one by one, and delete them individually. If you have dozens or hundreds of rows, this could be time consuming.
• You could sort the rows alphabetically, which would put all the blank rows together, and then delete that group of blank rows. However, there will be times when you may want to maintain the order of your list, so this would not be an option.
• You could filter the list for blanks, select these rows (i.e. visible cells) and delete them.
• You could use a little-known shortcut to quickly select the blanks in your list and delete them.

This week I will cover option number 4 and next week I will cover option number 3. The first two are self-explanitory and usually are not your best choice, so I won't discuss those.
Suppose you receive a list of data that contains blank rows that you want to remove without affecting the order of the list. This technique will allow you to delete all rows that have blank cells in a particular column. Here's the fastest way to do it.

1. First, before you make any drastic changes to any workbook always make sure you have a backup copy or a recently saved copy in case you need to restore the original data .
2. Now, select the cells in one column from the top of your list to the bottom.
3. Make sure that all the blank cells in this selected range are the rows you want to delete.
4. Press the F5 key on your keyboard (or select Edit, Goto).
5. Click the Special button.
6. Click the Blanks option and click OK. This will select all blank cells in the range you had previously selected.
7. Now choose Edit, Delete, select the Entire Row option and click OK.

If you work with large lists of data in Excel, this tip will save you a lot of time. If you don't work with large lists often, remember this tip anyway, because sooner or later you're going to need this.

All files open in the same window in Microsoft Excel

Issue

I have a problem with Excel 2007. All my files open in the same window.

Solution

• Go to explorer, choose tools, then folder options. Click the File Types tab.
• Scroll down to the XLS extension. Click the Advanced button. Choose "open", then click the edit button.
• At the end of the "Application used" entry, you'll probably see: /e
• After this, add: "%1" (be sure to include the quotes.)
• Then uncheck the "Use DDE" checkbox. Then click OK. (Windows re-checks it at some point for some reason, but it still works)
• Click on OK
• Now when you double-click a spreadsheet, it will open it in a new instance of Excel