How to delete blank rows in a worksheet using Macro? Using macro, one can delete blank / empty rows easily. In below, I am trying to share the step by step procedures to be familiar with this macro and what’s the logic behind it. Before begin let assume that the current worksheet is having data in A1: D500. You can change the data range as per your actual requirement. In the below data table there are four records in row 1,2,7 & 9. If you run the macro then the data will be appear as follows: Right now we will concentrate on how to create the said macro to delete the empty/blank cells. Step 1: Go to View Menu and selet View Macros marked in red color. In Macro Name: Type the macro name say “DeleteBlankCell_SameSheet”. You may write different macro name. Click on Create button Step 2: Next type the following commands inside the window. Now close the window. To Run the macro you may go to View--> Select Macro--->View Macro--->select macro name “DeleteBlankCell_SameSheet” and Click on Run. Or you may assign the said macro and on clicking the assigned object this macro will run. To do the same Go to Insert--->Click on shape Select any shape place it on cell where you want. Right click on it and type say “Delete Blank Cells” . To assign the macro click on Assign Macro as follows: Select “DeleteBlankCell_SameSheet” and click on OK button. So next time when you click on it will delete all the blank rows within the range A1:D500. To save the macro in excel file should be save as .xlsm (Excel2007) Please refer the following to understand the logic behind the mac ro commands as written above. For any further clarification you may leave your comment.
1 Comment
How to search a Value having Multiple search criteria? In above table we see that there are items, having same size with different make. In this case if we want to see the price of any item with specific size and make then which function we need to use? We know about VLOOKUP function. Using VLOOKUP we can get value of one criteria. However if there is more than one criteria then how could we search the value? Suppose we want to see the price of Tablet 10" (make Micromax) in such case we should use the INDEX,MATCH function. How to use INDEX, MATCH function? Step1: In cell G15, put the function as mentioned above and press ENTER. We will see that in cell G15 the Price as #VALUE Step2:In cell G15, press F2 and PRESS CTRL+SHIFT+ENTER. Now we will see the change in function and Price Value of the given criteria ( marked in red color box ) Want to understand the function in details?
Here D3:G12 is the array that INDEX uses to find the value MATCH(1<--represents a True Boolean result in given criteria that(to match D15 in D3:D12) and (to match E15 in E3:E12) and (to match F15 in F3:12), If the search criteria that D15, E15 & F15 do not match the array will have a 0<--represents a False Boolean result. 4 means the column number in array table from where value to be search http://www.caclubindia.com/share_files/dynamic-chart-in-excel-68545.asp
http://www.caclubindia.com/share_files/consolidate-report-using-offset-function-68056.asp http://www.caclubindia.com/share_files/pivot-table-on-protected-sheet-68033.asp http://www.caclubindia.com/share_files/how-to-make-cumulative-sum-on-certain-criteria--68110.asp http://www.citehr.com/537701-help-me-any-advanced-excel-format-any.html Often there is requirement to track invoice wise payment record. The user instead of maintain it in accounting software rather prefer to keep the record simply in excel file and want to track accordingly. In such case this excel file may appear helpful and meet basic requirement. Please download the file, read the instruction and try it. For any feedback please leave your comment.
In excel one can generate Consolidate Report as per the requirement using OFFSET function. The report can be generate within a second..More over it is convenient too handling large amount of data and generate relevant report. For your hands on experience you may refer the attached file and try it yourself. For any clarification soliciting your comment.
Using Macro in Excel, Pivot Table Report works well on Protected Sheet. You can get control over Pivot Table Report and customise as per your requirement.
In franchise business often there is need of issuing franchise agreement, MOU, etc. to the marketing team members on a regular basis. This Franchise Agreement /MOU is a valuable document not just of its monetary cost, however it has legal importance too. In general case, the franchise agreements/MOUs issued to the marketing team members will be used for signing by the business prospects, few may be lying in hand as physical stock as unused or invalid etc. If you need to track the movement of above documents in a systematic way and in very effective manner, then this attached macro based excel file,named "Agreement Utilisation.xlsm" may be useful to your organisation. THIS FILE WILL BE RUN ONLY IN EXCEL 2007 or higher version. Please download the file. Click on the file to open, set security level MACRO ENABLE,read the guidelines carefully and do experiment it.
|
|