Issue With deleting rows

[richard1234567891011]

Hi Guys.

I have an issue in this file. I have a Sheet 2 with formula references in another Sheet 1. The issue is related with deleting rows in sheet 1, because in sheet 2, in the other cells, on the right side of the formula references, there are written informations.
Practically when I delete a row in sheet 1, It will be deleted also in sheet 2 the information but all the written informations in Sheet 2 will be moved up.

It is not easy to explain, for this reason here you have the file.

If you delete for example all the buildings in ROMA, in sheet 1, you will have this.

[/URL][/IMG]

The issue arise because, in the Sheet 2, the description will now not follow the set of buildings. So the question is: Is it possible to link in somehow the cells? I have to delete the description of the building when the related row in the other sheet is deleted. Do I need macros? Or It can be done in another way instead? (I would like to not use macros)

[/URL][/IMG]

I hope it is clear. This is the file:

https://www.dropbox.com/s/jxvzx4z25sji53w/Example.xlsx?dl=0


Thank you guys.

 

[mumps]

For clarification, if you delete "Roma" from Sheet1, you want "Roma" and the "Descrizione" for "Roma" to be deleted form Sheet2. Is this correct? Are you deleting the rows in Sheet1 manually? I think you will need a macro to do what you want. Would it work for you if the macro prompts you to enter the "Città" that you want to delete and then the macro deletes the rows in both sheets?

 

[richard1234567891011]

For clarification, if you delete "Roma" from Sheet1, you want "Roma" and the "Descrizione" for "Roma" to be deleted form Sheet2. Is this correct? Are you deleting the rows in Sheet1 manually? I think you will need a macro to do what you want. Would it work for you if the macro prompts you to enter the "Città" that you want to delete and then the macro deletes the rows in both sheets?

Yes it is correct, And Yes, I want to delete the rows in sheet 1 manually, at least there isn't another way. And yes, I would like to not use macro if it is possible. But, if it is not possible, with the macro in your way would be perfect.

 

[kvsrinivasamurthy]

Instead of Sheet2 add the column Descrizione in Sheet1. So that when rows are deleted Descrizione column data also updated.

 

[richard1234567891011]

Instead of Sheet2 add the column Descrizione in Sheet1. So that when rows are deleted Descrizione column data also updated.

I cannot do it in this way. This is a simpler version. I have many many informations in sheet 1 and many another sheets like sheet 2. I would be a mess if I put all the descriptions in sheet 1.

 

[richard1234567891011]

I cannot do it in this way. This is a simpler version. I have many many informations in sheet 1 and many another sheets like sheet 2. I would be a mess if I put all the descriptions in sheet 1.

I cannot do it in this way. This is a simpler version. I have many informations in the original sheet 1 and many another sheets like sheet 2. I would be a mess otherwise.

 

[mumps]

Start by removing the formulas in column A of Sheet2. Then do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro into the empty code window that opens up. Press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key.

Option Compare Text
Sub DeleteRows()
    Application.ScreenUpdating = False
    Dim response As String, fnd As Range, sh1 As Worksheet, sh2 As Worksheet
    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")
    response = InputBox("Please enter the Città you want to delete.")
    If response = "" Then Exit Sub
    With sh1.Cells(2, 1).CurrentRegion
        .AutoFilter 1, response
        sh1.AutoFilter.Range.Offset(1, 0).EntireRow.Delete
        Set fnd = sh2.Range("A:A").Find(response, LookIn:=xlValues, lookat:=xlWhole)
        If Not fnd Is Nothing Then
            fnd.EntireRow.Delete
        End If
    End With
    sh1.Cells(2, 1).AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[richard1234567891011]

Start by removing the formulas in column A of Sheet2. Then do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro into the empty code window that opens up. Press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key.

Option Compare Text
Sub DeleteRows()
    Application.ScreenUpdating = False
    Dim response As String, fnd As Range, sh1 As Worksheet, sh2 As Worksheet
    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")
    response = InputBox("Please enter the Città you want to delete.")
    If response = "" Then Exit Sub
    With sh1.Cells(2, 1).CurrentRegion
        .AutoFilter 1, response
        sh1.AutoFilter.Range.Offset(1, 0).EntireRow.Delete
        Set fnd = sh2.Range("A:A").Find(response, LookIn:=xlValues, lookat:=xlWhole)
        If Not fnd Is Nothing Then
            fnd.EntireRow.Delete
        End If
    End With
    sh1.Cells(2, 1).AutoFilter
    Application.ScreenUpdating = True
End Sub

Thank you. But how can I add more sheets to this macro? I mean, I need more sheets with the same deleting effect, what is happen to sheet 1, I need it also to other sheets.

thank you.

 

[mumps]

Please upload a copy of your workbook that contains the additional sheets.