Add new rows + number of rows through dialogue box

[jokris]

The code below is assigned to a button.
When button is pressed, a dialogue box asks me to put marker where new rows (copy of first hidden row) shall be inserted.
However, the rows are inserted where my selection is before I pressed the button, independent of where I tell my dialogue box where it should insert new rows.

How can I tell the dialogue box where I want new rows?
And is it possible to let it ask me also how many rows? (as of now, I have to insert a value at cell N2)

Please Login or Register  to view this content.

[Kevin#]

Try this.
To select the cell where row(s) to be inserted either enter the cell ref or select with pointer

Please Login or Register  to view this content.

[jokris]

I tried and the dialogue box works!
However, it inserts blank rows. Is it possible to insert rows that are copies of my first hidden row? (as in my code above)

Try this.
To select the cell where row(s) to be inserted either enter the cell ref or select with pointer

Please Login or Register  to view this content.

[Greg M]

Hi again,

Thanks for your feedback and the Reputation increase on your previous post - much appreciated

See if the following code does what you need for this:

Please Login or Register  to view this content.

The highlighted values may be changed to suit your own requirements.


Hope this helps - please let me know how you get on.

Regards,

Greg M

[jokris]

Thank you so much Greg for taking your time.

The code works! But is it possible to avoid using cell N2 and instead having one more dialogue box directly after the first one, asking me how many rows I would like to insert?

[Greg M]

Hi again,

And is it possible to let it ask me also how many rows? (as of now, I have to insert a value at cell N2)

See if the following code does what you need:

Please Login or Register  to view this content.

The highlighted values may be changed to suit your own requirements.


Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

[jokris]

Greg, it works great now!
Have to test everything more tomorrow, but right now it is doing just what I wanted to.
Thank you so much

(trying to give you more reputation points but am told to spread them out more)

[Greg M]

Hi again,

Many thanks for your latest feedback and also for trying to give me even more Reputation points - very much appreciated

You're very welcome - I'm pleased that I was able to help out.

Please feel free to ask if there's any further information you need.

Best regards,

Greg M

[Kevin#]

And this is earlier code amended to include the copying of row1

Please Login or Register  to view this content.

[jokris]

If I perform this procedure on "worksheet A" with its hidden first row, would it be possible to automatically make the same thing happen on "worksheet B" and "worksheet C" (but not "worksheet D" ?
Each of Worksheet B and C have a unique hidden row at the top too.

So for chosen worksheets, I would like the same procedure to happen automatically when I do this on "worksheet A"

[Kevin#]

Note that
rng is now Dim as string
and that next line down has .address at the end
and that rng has become Range(rng) in the other lines

Please Login or Register  to view this content.

[Greg M]

Hi again,

Try the following code and see if it does what you need. It incorporates data validation for both of the User input values (number of new rows and location of new rows):

Please Login or Register  to view this content.

The highlighted values may be changed to suit your own requirements.

The routine will work regardless of what worksheet is active when the routine is called.


Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

[jokris]

I tried both your codes and they work excellent!

Only have one concern left now as it seems.
When people add or delete rows, I don't want them to do it on the first say 5 rows, which has information that needs a fixed position.

So I would only like them to add or delete beginning with the 6th row. So trying to insert or delete something within the five first rows should result in an error message of some kind.

[Greg M]

Hi again,

Many thanks for your feedback.

See if the following code does what you need - data validation has been incorporated into two separate function routines to make the main routine a little easier to follow

Please Login or Register  to view this content.

As before, the highlighted values may be changed to suit your requirements.

Hope this helps - as always, please let me know how you get on.

Regards,

Greg M



P. S. Many thanks also for the Reputation increase - much appreciated

[Kevin#]

Please Login or Register  to view this content.

Given that you now have 2 solutions can you please mark the thread as doubly solved
thanks

[jokris]

Your codes work when adding new rows in several sheets too! Amazing knowledge...
is it also possible to do the opposite? That is, deleting selected rows (starting from row 6) on one of the worksheets and thus deleting the same rows on the other worksheets?

BTW, can I buy you guys a beer or something?

[Kevin#]

How about trying the obvious out for yourself and you providing the answer?
There are 3 lines that are no longer required and 3 that need a very minor alteration

[jokris]

I will try later on today to find a solution to this.
Hopefully I can figure out how this will work and compare with deletion formula.

I would love to learn VBA for myself and understand how it works.
Otherwise I would gladly pay a symbolic sum if I can't come up with an answer.

[Kevin#]

hint:
the antonym of "insert" is "delete"

[Greg M]

Hi again Johan,

See if the following code does what you need - if you study it you can see how breaking it down into separate routines allows you avoid duplicating large amounts of code for the Add Rows and the Delete Rows processes:

Please Login or Register  to view this content.

As before, the highlighted values may be changed to suit your own requirements.


Feel free to ask if you require further information about anything.

Hope this helps - as always, please let me know how you get on.

If you really feel the need to "buy me a beer" I'd be very pleased if you'd make an equivalent donation to The Dogs Trust

Best regards,

Greg M

[jokris]

The Dogs Trust at https://www.dogstrust.org.uk/ I guess? Will do

I am looking more for something similar to this style below that I already use (but code below only works for the current worksheet, not multiple worksheets A, B and C. And problem is the code can erase row 1-5 which I want to stay intact). The code first asks me to mark the rows I want to delete, before I delete them. I think that is good because it makes it possible to mark say row 6 and row 9 (but not row 7 and 8).

Please Login or Register  to view this content.

[Greg M]

Hi again,

Thanks for noting The Dogs Trust

As you want to use different approaches for specifying the parameters in the Add Rows and Delete Rows operations, we can't use as much "common code" as before.

The following code allows you to specify non-touching rows for deletion, and displays a message asking you to confirm the delete operation:

Please Login or Register  to view this content.

Hope this helps - as always, please keep me posted!

Regards,

Greg M

[Greg M]

Hi again Johan,

Thanks for the Private Messages re the above.

Further to your post http://www.excelforum.com/excel-prog...-password.html, see if the attached workbook does what you need. I can't access the Commercial Services forum to post my reply there.

The code includes a "Confirm Deletion" message which isn't mentioned in your specification, but if you don't want to use it just change the value in:

Please Login or Register  to view this content.

from True to False

Hope this helps - as always, please let me know how you get on.

Best regards,

Greg M

[jokris]

The code works great, confuses me though why it won't work for my file.
Sent you my file through private message and explanation of how interval of insertion and deletion of rows was implemented.
The last possible place to insert rows is based on if the row doesn't contain anything. Then it makes it impossible to insert rows after this empty row (which means that there can't be an empty row above where you insert rows.

I must say I am a little amazed over how fast your code is!

I'll be away for some hours, but will be in touch again

[Greg M]

Hi Johan,

Thanks for the Private Message and the workbook.

As per your specification:

5) If selected row is more than the row number that contains the word "maximum", a dialog box tells it is not possible to add copies here. Code ends.

the word "Maximum" is inserted (once per column) in various cells in Column A of the worksheets in the version I posted here - this determines the point beyond which rows may not be added or deleted on that worksheet.

For the workbook you sent me, when I inserted the word "Maximum" in Cell A19 of the worksheets Multiplesheet1, Multiplesheet2 and Multiplesheet3 (and "pointed" your buttons at my routines) everything seemed to work correctly.

Please keep me informed and feel free to ask about anything that's not clear to you.

Best regards,

Greg M



P. S. Your comments about the commercially-obtained software were interesting! I'd certainly be interested in seeing it if you'd care to send it to me.

[jokris]

I sent you the file Greg and tried to explain what each of the six included codes in Module1 do (they should fill their purposes).
Feel free to test it out.

There is no "maximum" word as a placeholder in the end, but rather EndRow in MultipleSheets and empty row right after the last row with formulas and formatting as the last place to insert rows in SheetA, Figure and Object

It bugs me somewhat that when performing the code it takes some time, especially compared to your code which seems much faster... and I have no idea why. Perhaps it is going through too much data or something?

[Greg M]

Hi Johan,

I've made some changes to the file you sent me. I can post it here, but if you'd prefer that I didn't, send me your email address in a Private Message and I'll email it to you.

Regards,

Greg M

[Greg M]

Hi Johan,

Thanks for your email and workbook.

I'll post the latest version of my model workbook here in case anyone else has been following this thread.

Regards,

Greg M