Skip to main content

Call a custom Script from Excel


It is quite common that we receive the data in Excel and several times we have to remove unwanted characters from columns or modify the data as per the requirement. To avoid manual fixes of these kind of requirements, we may use Macro to automate the process which not only increase productivity but also avoid manual errors. We can easily create Macro to quickly fix it. Here are the steps to create Macro and run it.

  • Open Excel
  • Goto View tab on Main menu

  • Click on 'View Macros'. This should open a Macro popup.
  • Enter Macro Name and click on + icon.
  • Clicking on + icon should open Macro Coding screen(as shown below).
  • Add your code in the function.
  • Click on Save icon to save the file with Macro.
  • Click on Run icon to run the Macro code.
  • The above code should remove additional line breaks from columns.
Reference Code(to remove line breaks from columns):
Sub MyCustomMacro()
Dim MyRange As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For Each MyRange In ActiveSheet.UsedRange
If 0 < InStr(MyRange, Chr(10)) Then
MyRange = Replace(MyRange, Chr(10), "")
End If

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub


Popular posts from this blog

Cannot alter the login 'sa', because it does not exist or you do not have permission.

Working on projects, it can happen that 'sa' account gets locked. If it is on local machine OR development boxes, onus would be on you to fix it. If scripts and SQL steps are not working, this might help you fixing the issue. Steps to unlock 'sa' account and resetting the password. 1. Open SQL Server Configuration Manager 2. Select SQL Server Services -> 'SQL Server' service. 3. Right click on 'SQL Server' service and click on "Startup Parameters". For 2008, server "Startup Parameters" are inside Advanced tab.   4. Add '-m' in startup parameters as shown above and click on 'Add'. This will put SQL server into 'Single User Mode' and local admin will have 'Super User' rights. For 2008, server you have to add ':-m' in the last of the existing query. 5. Save the settings and Restart the service. 6. Now open the SQL Server Management Studio and connect to database using 'Windows A

Could not load file or assembly 'Microsoft.Web.Infrastructure'

Could not load file or assembly 'Microsoft.Web.Infrastructure, Version=, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified. What 'Micorosoft.Web.Infrastructure' does? This dll lets HTTP modules register at run time. Solution to above problem: Copy 'Micorosoft.Web.Infrastructure' dll in bin folder of your project and this problem should be resolved. If you have .Net framework installed on machine, this dll should be present on it. You can search for this dll and copy it in your active project folder.   Alternatively,  you can install this dll using nuget package manager PM> Install-Package Microsoft.Web.Infrastructure -Version 1.0.0 Happy coding!!

Git merge from one repo to another repo

This blog is for those who are looking for merging code from one repo to another repo. Why I will merge code from one repo to another? I forked from one git repo( may be some public git repo ) and did some cutomization on existing code. Occasionaly, features are being introduced in main git branch and I would like to get all those features in my own git repo. In this scenario, I would like to merge latest changes in my git repo. Here are few simple steps which will merge code from one git repo to another- 1. Clone the repo1(source git repo) > git clone   > git pull 2. Clone the repo2(destination git repo) > git clone > git pull 3. Goto repo2 (destination git folder) and checkout your prefered branch > cd repo2 > git checkout master>  4. Use below command to see the remote branch associated with your destination git repo > git remote -v  origin