Dynamic Range Selection in Macros code

In Excel we all face the issue while recording the macro, that the range selected is always a set.

So whenever there are changes in the rows like if added or removed then it should adjust automatically.

Here is the solution:

Let the list range in the recorded code is set as "A1:D15". 

Change it to Range("A1").CurrentRegion, so the range will automatically adjust, if rows are added or removed.

Ways to change the image background

1) Easiest Method - Online using PhotoScissors

Step 1: Select the photo you want to edit

Open PhotoScissors online and click the Upload button then select an image file on your local PC. The program opens the image and displays it in the browser:

The left part of the screen is for editing, while the right part displays the final result.

Step 2: Select the background and the foreground

Now, we need to tell PhotoScissors, where the background is. Normally, you would have to tinker with various selection tools, but in PhotoScissors the process is easier. Click the green marker tool on the toolbar and mark objects that are on the foreground. Then select the red marker tool and mark background objects the same way.

Simple, right? Green is for objects that should stay, red is for background you want to change. Note that as you mark the image with green and red tools, the preview at the right side reflects changes you make. By default, PhotoScissors applies transparent background to the picture:

Make small adjustments to green and red zones if necessary. Remember, you don't have to paint the entire background with red, a few rough strokes is enough.

Step 3: Change the background

Now, to replace background of the photo, switch to the Background tab in the right menu.

On the Background tab, select "Image" in the dropdown, then click the "Select Image" button and select what image you would like to use as a new background.

Nice! This background looks much better. And that didn't take too long. Finally, save the photo with the replaced background by clicking the Save button on the toolbar.

2)  Using Paint

Follow link: 

https://www.wikihow.com/Change-an-Image-Background-in-MS-Paint-(Green-Screen)

3) Using Photoshop

Follow link: 

https://blog.hubspot.com/marketing/how-to-remove-background-image-in-powerpoint-design-ht

Find Cells containing Formulas in Excel

Method 1: Using ‘Go To Special’ Option:

1. With your excel sheet opened navigate to ‘Home’ tab > ‘Find & Select’ > ‘Go To Special’. Alternatively, you can also press ‘F5’ and then ‘Alt + S’ to open the ‘Go to Special’ dialog.

2. Next, in the ‘Go to Special’ window select the ‘Formulas’ radio button. After checking this radio button you will notice that few checkboxes (like: Numbers, Errors, Logicals and Text) are enabled, these checkboxes signify the return type of the formulas.

So, if you select ‘Formulas’ radio button and only check the ‘Numbers’ checkbox then it will just search the Formulas whose return type is a number. Here in our example we will keep all of these return types checked.

3. After this click the ‘Ok’ button and all the cells that contain formulas get selected.

4. Next, without clicking anywhere on your spreadsheet change the background colour of all the selected cells.
5. Now your formula cells can be easily identified.

Method 2: Using a built-in Excel formula:

Excel has a formula that can find whether a cell contains a formula or not.

=ISFORMULA(reference)

Here ‘reference’ signifies the cell position which you wish to check for the presence of a formula.

For example: If you wish to check the cell ‘A2’ for the existence of a formula then you can use this function as =ISFORMULA(A2)

This function results into a Boolean output i.e. True or False. True signifies that the cell contains a formulas while False tells that cell doesn’t contain any formulas.

Method 3: Using a Macro for identifying the cells that contain formulas:

I have created a VBA Macro that can find and colour any cells that contain formula in the total used range of the Active sheet. To use this macro simply follow the below procedure:

1. Open your spreadsheet and hit the ‘Alt + F11’ keys to open the VBA editor.

2. Next, navigate to ‘Insert‘ > ‘Module‘ and then paste the below macro in the editor.

view plaincopy to clipboardprint?

1. Sub FindFormulaCells()  
2. For Each cl In ActiveSheet.UsedRange  
3. If cl.HasFormula() = True Then  
4. cl.Interior.ColorIndex = 24  
5. End If  
6. Next cl  
7. End Sub  

3. For running this formula press “F5” key.

4. This Macro will change the background colour of all the formula containing cells and thus makes it easier to identify them easily.

What is syspolicy_purge_history

This job is created by default when an instance of SQL Server 2008 is installed or upgraded to SQL Server 2008.

SQL Server 2008 introduced a new feature called Policy Based Management. A Policy can be something like xp_cmdshell should not be enabled on an instance. When a policy runs it stores the results in MSDB. Over a period of time, this may will cause unnecessary data getting piled up in MSDB. The job syspolicy_purge_history prunes the data older than the the days defined in HistoryRetentionInDays property of Policy Management.

In the above screenshot it is set to 0 which means that the Policy Evaluation History will never be deleted. So on this instance the job is running just like that 

If this job is missing for some reason, it can always be recreated using the below query

EXEC msdb.dbo.sp_syspolicy_create_purge_job