Here you are going to learn trick about how to find the blank cells in the spreadsheet data and remove them or remove entire row using Excel VBA (Macro) programming with Example program.
In order to find and remove the blank cells from the data in excel spreadsheet, we need to find the last row of the range. Once we find the last row, then we can use loop to check each cell or row in the data using if condition. Once we find the blank row or cells, we can just write a code to remove that particular cell or row. Similar way for all rows we can repeat the loop
Here you are going to learn the way how you can get the last row of the range using various types techniques in Excel VBA programming. Excel vba also known as excel macro programming. Once you find the last row, you can do many things to automate the activity. Hence finding last row in the given excel range is very important concept in Excel VBA. There are many ways you can get last row in the range. 1) Using usedrow 2) using cells 3) using Range in the respective spreadsheet.
Creation of named ranges are explained in deep as we have already seen in the vlookup tutorial.
Previously when we were using excel 2003 this named range was used frequently to automate the excel activities using Excel macros. In stead of ranges, we were using the named ranges as those fixed with particular range of cells, rows or columns. In order to create named range, we just need to select the required range of cells that you want to name, then go to cell address bar and give the desired name, once you give press enter tab. That’s it next if you select that range, you can see the given name for that range in the same cell address bar. You can check the below screen shot for your reference.
This is the simple method which we can use. we have another method for creating the named range. That option is available in the Formulas Menu -> Name Manager. You can click the name manger and select the desired sheet and range of cells in the respective text boxes which will be available in the Name Manger option, then you can give the desired name. You can check the below screen shots for your understanding purpose.
Once you get the above name manager option, you can click the new option or edit option(if you want to change any given name) to name the range of cells, rows or columns as per your requirement. Once you click the new or edit option in the name manger, you get the below option box
Hope you understand basic excel option named range. If you have suggestion, please comment in the below comment box to encourage us.
There is an option called Goal Seek to find the value of principle amount or number of years or rate of interest when tow values available among there 3 then we can find the 3rd value using this option. Usually this option very useful in Banking sector. Also when you situation to calculate the PNR/100.
This is one of excellent option in the excel to easy calculation process for interest related activities.
Here you are going to learn the way you can do the data validation in Excel. Data validation option can be used to validate the data in excel. Data in the sense there might be numbers or text based on the requirement. This is the one best features in the excel spreadsheet to restrict end users from committing errors by selecting incorrect data. Also this feature can be used to create many options to end user while updating statements or forms. Data validation has many options and varieties. I am going to give you some of examples to explain you in depth for all the options available in this feature.
Creating hyperlink is an easy method where you can just go into particular cell and right clink then select the option called hyperlink option or you can just go to insert menu then select the Hyperlink option which is available in the menu bar. Once you select hyperlink option within these 2 options, you have to select location either within the document or outside the document.
Hyperlink using mouse right click:
Hyperlink using Insert Menu:
Once you select either one of the methods in the above, you will get the below screen to link with other sources.
In the above screen we need to choose correct option based on our requirement. If you need to link with web page or Place in the current Document options most of the times in the Excel.
Here i am going to explain you the way how you can learn all the excel formulas easily within less time from basic excel to advanced excel.
Learning excel is very easy. Once you learn, you must start using formulas in the live scenarios. That’s where your real learning part would be started. Mainly you have to focus on important formulas and options that are available in the Microsoft Excel.
As soon as you learn the important formulas and options then you can start developing the dashboards, reports and templates for daily, weekly, monthly, quarterly and yearly based on the team’s requirement. This is how Excel learning procedure can be started from basic level to advanced level.
Here you are going to learn the way how you can connect with any database like MS Access application or Sql. When you speak about the connecting with database, you have to create connection string and adding suitable provider to open the connection of database. Once you know these two things, you can easily write the program to connect database. Then we have to write the query to execute respective task. Example “Select * From Emp”. Also we need to define Resultset and Connection objects at the initial stage. At the end of the program need to close the connection. This is the procedure we need to follow.
Here you are going to learn about the advantages and disadvantages of VBA ( Visual Basic Applications )
Removing the non value added things like copy paste work, repetitive works.
Reducing the TAT ( Turn around time) for preparation of the report, template or dashboard
Reducing the formulas burden from the excel reports like when you give manual formulas in the excel, usually it hangs for a while to calculate formulas ( when you have more formulas or data in the sheet ). You can keep the formula in the coding part to overcome this issue.
Can be removed the manual intervention to prepare the reports and improve the accuracy of the report. Because when you are preparing manually, accuracy would depending your mental condition whereas excel macro does not have that issue. It prepares based on the provided inputs.
Providing the FTE (Full time employee) reduction benefit for the organization in terms of saving money to organizations.