Solved: Delete row (numbered) all other rows (numbers) change in sequence

[Nurofen]

I have a speadsheet with numbers in column (1.01,1.02.1.03), If i delete 1.02 is there for all the other numbers to resequence themselves to read
(1.01,1.02).

Thank you for your help in advance.

Regards

Anit

[MWE]

I have a speadsheet with numbers in column (1.01,1.02.1.03), If i delete 1.02 is there for all the other numbers to resequence themselves to read
(1.01,1.02).

Thank you for your help in advance.

Regards

Anit

there are ways to do this. A few questions:

1. I assume you mean that in a spreadsheet with multiple rows, you have a column used to "number" each row, for example, 1.01, 1.02, etc
2. I assume by "delete 1.02" you mean delete the row containing 1.02
3. You provided 1.01, 1.02, 1.03 as an example. What happens after 1.99?
4. Do you want to do this using VBA or without VBA?

[mdmackillop]

In A1 1.01
In A2 =A1+0.01 and copy down

[MWE]

In A1 1.01
In A2 =A1+0.01 and copy down

I don't think it is quite that simple. You are assuming that the identify scheme is numerical and runs as a numerical sequence. I await confirmation from Nurofen that the assumption is correct. I suspect that row ID is really an indexing scheme and some "numerical values" are not there.

[Nurofen]

1. I assume you mean that in a spreadsheet with multiple rows, you have a column used to "number" each row, for example, 1.01, 1.02, etc
2. I assume by "delete 1.02" you mean delete the row containing 1.02
3. You provided 1.01, 1.02, 1.03 as an example. What happens after 1.99?
4. Do you want to do this using VBA or without VBA?

1. Yes
2. Yes the whole row will be deleted
3. There are different areas of interest so one area may only reach 1.16, the next area would then start at 2.01 (sheet attached).
4. I would say vba, but would like to see what can be done without vba

Just a add-on to the last question:
5. The link refers to another sheet and area of where the data is stored can the the data be cut and placed into another sheet when the numbered link is deleted so the data and links are up-to-date


Regards
Nurofen

Thankyou for all your help inadvance

[MWE]

1. Yes
2. Yes the whole row will be deleted
3. There are different areas of interest so one area may only reach 1.16, the next area would then start at 2.01 (sheet attached).
4. I would say vba, but would like to see what can be done without vba

Just a add-on to the last question:
5. The link refers to another sheet and area of where the data is stored can the the data be cut and placed into another sheet when the numbered link is deleted so the data and links are up-to-date


Regards
Nurofen

Thankyou for all your help inadvance

attached please find an updated spreadsheet with the functionality you requested. I added a few things:

• it deletes one or more rows
• it checks to make sure that no "title" row is in the set of rows to be deleted

Simple select one or more rows and click on the command button.

I copied the data from sheet1 to sheet2 for testing purposes. I used sheet3 as the target sheet to which deleted rows are copied

The code is "brute force", nothing elegant. But it is easy to understand.

You will find a 2nd procedure, xlLastRow, that computes the last populated row of a target worksheet. That is used to figure out where copied rows belong.

Let me know if this works as you want.

[Nurofen]

MWE Thank you for that mate.

1. The re-indexing is prefect, is there a way of deleting the number that appear out of boxes in column A.

2: Say I wanted to delete the row sheet1 and also delete a named range on sheet2. On sheet(1) I delete row(s) 2 with 1.02 which is linked(this refers to a range A4:L23 on sheet2)to the range named on sheet2, which will also be deleted
3): if a new number is added with a new name is possible to have user be asked to input the named range that it refers to on sheet(2), so when clicked or deleted, it either refers them to the range of data on sheet(2) or deletes it.

Thank you for help in advance

[MWE]

MWE Thank you for that mate.

1. The re-indexing is prefect, is there a way of deleting the number that appear out of boxes in column A.

2: Say I wanted to delete the row sheet1 and also delete a named range on sheet2. On sheet(1) I delete row(s) 2 with 1.02 which is linked(this refers to a range A4:L23 on sheet2)to the range named on sheet2, which will also be deleted
3): if a new number is added with a new name is possible to have user be asked to input the named range that it refers to on sheet(2), so when clicked or deleted, it either refers them to the range of data on sheet(2) or deletes it.

Thank you for help in advance

Let's address your new needs one at a time:

1. I do not understand what you want. Assuming that "in boxes" means cells with borders: in the sample spreadsheet you provided, all numbers in ColA are "in boxes". What numbers do you mean?

2. Your linking (or hyperlinking) from Sheet1 is to individual cells in Sheet2. For example, the link from cell A5 (with text 1.02) is to cell A2 on sheet2. That said, if you wish to delete the linked range (a single cell or a multi-cell range) on some other sheet associated with (linked from) a particular row on Sheet1 when the particular row is deleted, yes that can be done. Do you wish to delete the range or "clear the contents" of the range. If you delete the range, adjacent cells move around.

3. The easiest way to do this would be to add a button (Add Row) that would add a row above the currently selected row and prompt the user for the text for colB in the new row, and the sheet and range that is to be linked to the new row. I assume you would want to reindex after any add.

As this gets more complicated, it would be wise to protect relevant sheets so operations are only done via command buttons

[Nurofen]

1. I do not understand what you want. Assuming that "in boxes" means cells with borders: in the sample spreadsheet you provided, all numbers in ColA are "in boxes". What numbers do you mean? ignore me on this one I worked out the problem, if you delete e.g. 6.04 the re-indexing numbers appear out of the boxes. I will use a title colored white to stop this from happening.

2. Your linking (or hyperlinking) from Sheet1 is to individual cells in Sheet2. For example, the link from cell A5 (with text 1.02) is to cell A2 on sheet2. That said, if you wish to delete the linked range (a single cell or a multi-cell range) on some other sheet associated with (linked from) a particular row on Sheet1 when the particular row is deleted, yes that can be done. Do you wish to delete the range or "clear the contents" of the range. If you delete the range, adjacent cells move around. I would be hyperlinking to a multi-cell range on sheet2, the data will always be the same width(A:K) but the length will be different in all cases. link 1.01 = (A13:K25), link 1.02 = (A27:K50). When 1.01 is deleted can (A13:K25) also be deleted and all other cells move up and the re-indexed on sheet1 reflects sheet 2.

3. The easiest way to do this would be to add a button (Add Row) that would add a row above the currently selected row and prompt the user for the text for colB in the new row, and the sheet and range that is to be linked to the new row. I assume you would want to reindex after any add. Yes

As this gets more complicated, it would be wise to protect relevant sheets so operations are only done via command buttons Ok

MWE thank you for taking the time to help.

[MWE]

re your upgrades:
1) attached please find new version of spreadsheet with the bug you discovered fixed. I did not consider the problem of "no next group". The revised spreadsheet now does what it should when you delete a row in the last group. I also froze rows 1:2 so the DeleteRow button is always visible.

2) yes, we can delete ranges as you wish

3) I will update the attached a little later today with requests #2 and #3 (and the auto protect feature) and should post back before the end of the day.

[Nurofen]

MWE,
Thank you so much.

Nurofen

[MWE]

attached please find v004 of the work in progress. Updates include:

• Deleted rows are posted to "DeletedRows" sheet
• if deleted row has a hyperlink, the target is deleted
• Add Rows button to add one or more rows
• AddRows procedure adds rows just like the manual process, i.e., rows are added above the selection and the # of rows added = the # of rows selected
• after adding rows, relevant section is reindexed
• then user is asked (row by row) for "Name" and hyperlink range. The appl assumes that all links will be to the same sheet ("LinkSheet")

I changed the links for the 3 cells on Sheet1 to link to ranges in the LinkSheet sheet. I think everything you wanted is included. I did some testing and everything seems to work OK. I have not added the automated sheet protecting and unprotecting as I wanted you to play with it a bit and see if it is what you want.

Get back to me with anything that is not working as you wish or if you have additional requests (within reason) or when you are ready for the sheet autoprotect.

[Nurofen]

• Deleted rows are posted to "DeletedRows" sheet (thanks)
• if deleted row has a hyperlink, the target is deleted (thanks)
• Add Rows button to add one or more rows (thanks)
• AddRows procedure adds rows just like the manual process, i.e., rows are added above the selection and the # of rows added = the # of rows selected (thanks)
• after adding rows, relevant section is reindexed (thanks)
• then user is asked (row by row) for "Name" and hyperlink range. The appl assumes that all links will be to the same sheet ("LinkSheet") (thanks) After the name has been added I would the user to enter a further 5 cells across Name, House address, class, goal, color, date.

Once the row has been added and I click the hyperlink I recieve a error, I have attached print page of this, it maybe me doing something wrong.

Again thank you for all your help mate.(I'm unable to attach my file, will ASAP)

[MWE]

• then ... (thanks) After the name has been added I would the user to enter a further 5 cells across Name, House address, class, goal, color, date.

Once the row has been added and I click the hyperlink I recieve a error, I have attached print page of this, it maybe me doing something wrong.

Again thank you for all your help mate.(I'm unable to attach my file, will ASAP)

The bug in the hyperlink has been fixed (I had attached the wrong spreadsheet).

I updated the spreadsheet to provide the additional 5 cells for each added row and tweaked the sheet1 format to accommodate things. Is the Date the user enters today's date? If so, we can automate that.

[Nurofen]

MWE,
That is prefect mate, the date is random so can not automate.
is it possible to have the number on the link appear on the data top left cell

one last request:

if i wanted to print the data that is linked to say steve can that be done from a search feature or a drop down list.

Once again Thank you so much for all your help.

Nurofen

[MWE]

MWE,
That is prefect mate, the date is random so can not automate.
is it possible to have the number on the link appear on the data top left cell

one last request:

if i wanted to print the data that is linked to say steve can that be done from a search feature or a drop down list.

Once again Thank you so much for all your help.

Nurofen

I am not sure what you mean when you state, "is it possible to have the number on the link appear on the data top left cell". I assume that you are asking for the following:

• for any row on sheet1 that has a hyperlink
• add the row "index", e.g., "1.04" to the top left cell of the hyperlink target (range)

That can easily be done as long as the target cell is not being used for something else already.

Next, when you ask about "... print the data ...":
Printing the range associated with a particular hyperlink is fairly easy
One method would be to add a button called, say, "Print Data" that would print the link target data associated with the currently selected line. By selecting the row and then clicking the button, the row information (Index, Name, Address, etc) could also be provided on the printout.

[Nurofen]

• for any row on sheet1 that has a hyperlink
• add the row "index", e.g., "1.04" to the top left cell of the hyperlink target (range) yes ( Max - 1.04) attached sheet

That can easily be done as long as the target cell is not being used for something else already. (thanks)

Next, when you ask about "... print the data ...":
Printing the range associated with a particular hyperlink is fairly easy
One method would be to add a button called, say, "Print Data" that would print the link target data associated with the currently selected line. By selecting the row and then clicking the button, the row information (Index, Name, Address, etc) could also be provided on the printout. That would only print the data connected to row?, (if steve appears more than once I would like to be able to print each data range connected to steve, steve appears say 9 or more times i would to be able to print them all.

again thank you.

[MWE]

Per the example, you want both the Name and the Index in the top left cell of the hyperlink target. That is included in the attached version 6

Re the printing request. We can still use the selected row and Print Data button approach and add a search for all other identical names and print out those hyperlink targets as well. A few questions:

• if the row selected has Name = "Steve", we will print out all targets associated with Name = "Steve" in other rows. Do you want to also print out targets associated with alternate spellings, e.g., "steve", "STEVE", etc? (I assumed Yes)
• how do you want the printout grouped? The simplest way would be one target printout per page (I assumed one per page)
• how do you want to handle the situation where the entire range does not fit on a single page? Do you wish to have the output scaled to fit a single page? (I have done nothing about this yet)
• I have not done anything special should the selected row not have a hyperlink. Do you want to warn the user?
  

Given the assumptions above, the attached has this printing capability. When you click on Print Data, there will be a prompt asking if you wish to print out all instances of Name = ????. If you will always want to do that, we can remove that logic.

[Nurofen]

• if the row selected has Name = "Steve", we will print out all targets associated with Name = "Steve" in other rows. Do you want to also print out targets associated with alternate spellings, e.g., "steve", "STEVE", etc? (I assumed Yes) Yes
  
• how do you want the printout grouped? The simplest way would be one target printout per page (I assumed one per page) Yes
  
• how do you want to handle the situation where the entire range does not fit on a single page? Do you wish to have the output scaled to fit a single page? (I have done nothing about this yet) fit single page
  
• I have not done anything special should the selected row not have a hyperlink. Do you want to warn the user? Yes
  

Given the assumptions above, the attached has this printing capability. When you click on Print Data, there will be a prompt asking if you wish to print out all instances of Name = ????. If you will always want to do that, we can remove that logic. yes we can remove that logic, the case will always be to print all instances of steve. can we add a preview function where all the data for steve is just copied to a new sheet.


Thank you again

Nurofen

[MWE]

attached please find version 7. It contains the additional functionality requested. A new button, Preview Data, provides the previewing requested.

You owe me a beer the next time I am in the UK