VBA to insert and delete multiple rows

[wilsonyeoh]

Does anyone have codes to insert and delete multiple rows. I need to run a macro where a dialog box pops up requesting number of rows to insert and delete. Can anyone assist.

 

[wilsonyeoh]

Hi Norie. Me and my colleagues are trying to create a comprehensive template for our project. This is only the first part on data entry. We have a huge and growing data from both pdf and excel files to be entered into the spreadsheet. We need 2 buttons; one to insert and one to delete. The insert button inserts rows from row 5 downwards and delete button is total opposite. Reason for this is we are able to view fresh data without scrolling down. The insert and delete button must request number of rows required for action as sometimes we receive data in excel files that enable us to cut and paste. Hope you can help us here.

 

[Norie]

Are you sure adding/deleting rows is the right approach for this?

I can already think of some other methods but they might not be appropriate.

Perhaps if you posted some sample data before/after it might make things clearer.

!!!WARNING!! Only do the following if you have no other custom toolbars/menus etc!!!

As to the command buttons appearing on the toolbar, just right click any toolbar and select Customize...

Then find and select the toolbar in question and press Reset.

 

[MickG]

Hi ,to Run that last bit of code you will need 2 CommandButtons, one "Delete" ,one "Insert". That bit of code will do for both if you alter the line that has "Insert" to "Delete". As shown in the Code Remark.
The other bit of code should have left you Worksheet with 2 new items" "Delete Rows" and "Insert Rows". If you take that first bit of code from the "ThisWorkbook" code. and run it seperately it will delete the 2 Menu references.
I think thats what your asking !!!
Regards Mick

 

[wilsonyeoh]

Hi Norie & Mick.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Here is our existing spreadsheet created with the help of some good Samaritan in another forum. It works fine but somehow we need to tweak further to suit our desired results. Please note that this example is already simplified for easy understanding.
<o></o>
Register of Data (Sheet 1)<o></o>
Consists Column A for Work No, Column B for User and Column C for Price. Basically, these columns are updated daily and will run up to several thousand rows. The table indicates the individual Work No to be assigned to User at an allocated price. A User may be own more than one Work No hence all the data in Column A shall be unique while duplicates may appear in Column B.
<o></o>
Register of Work No (Sheet 2)<o></o>
Consists Column A for User, Column B for No of Works and from Column C to Column J for Work No. Data in Column A (User) are unique and derived from Column B of Sheet 1 while data for Column B to Column J (work quantity and Work No) are derived from Column A of Sheet 1. In other words, the tabulation indicates total work quantity and each Work No for individual User.
<o></o>
Register of Prices (Sheet 3)<o></o>
Similar to Sheet 2 but includes price for each Work No and total price for each User.
<o></o>
Few codes were written to tabulate both Column A (Work No), Column B (User) and Column C (Prices) of Sheet 1 in other formats as depicted in Register of Work No (Sheet 2) and Register of Prices (Sheet 3).
<o></o>
Two command buttons were created in Sheet 1 to insert and delete rows where the insertion and deletion starts from row 5 downwards. The current code only allows creation and deletion of 1 row at a time. We need a code to perform these actions base on our input such as a dialog box requesting quantity of rows to be inserted or deleted. The said code shall take into consideration of the impact on both Sheet 2 and Sheet 3 respectively.
<o></o>
One of the codes identifies only unique data in Column B (User) of Sheet 1 then create a row and paste them into both Column A (User) of Sheet 2 and Sheet 3. No rows will be created if the data are duplicates. This works as intended however the code only activates when individual data entry is perform on each row. It will not work when data are copied and paste into the columns in Sheet 1. Example, say that we inserted 5 empty rows and then paste them with the data from another worksheet no actions were performed. Similarly, when we insert a single row and entered the data individually the code perform as intended. We need to tweak the code to accommodate for these requirements.
<o></o>
Once the above requirement are fulfilled, we shall include some codes to prevent duplicate entry in Column A of Sheet 1 and a combo box as an option to sum the prices when we include the initial and final price columns later.
<o></o>
This task I supposed is challenging as I’ve enquired in other forums but with much success. Therefore, my colleagues decided to break it down by requesting minor favours and only build and integrate them later.
<o></o>
Hope both of you are able to assist us on this assignment.

 

[wilsonyeoh]

Can't seem to attach the example. How do I do that?

 

[Norie]

Is there any chance you could do this in something other than Excel?

It sounds like a good candidate for a database, eg Access, since you mention tables, relationhips etc.

PS I still don't see why you want to delete and insert rows, I really think seeing some sample data might help.

 

[wilsonyeoh]

Hi Norie. I would like to upload the data sample but can't figure out.

 

[Norie]

You cant upload a file.

You could try:

1 Uploading a file to a file-sharing site and posting a link to it.

2 Use one of the many tools, eg Excel Jeanie, Colo's HTML Maker etc, for displaying
a worksheet, there are plenty of links for those.

 

[wilsonyeoh]

Hi Norie. I'm in the office and there are many restrictions here. I'll upload it when I'm back home in 4 hours from now. Hope to hear from you soon.

 

[wilsonyeoh]

Hi Norie. I've uploaded the file here. Hope to hear from you.

http://rapidshare.com/files/239645135/Example_File-Test_2.xls.html