Macro that Delete a Row on a Sheet based on another Sheet

Question

I have a macro that consolidate the values on another sheet, and based on these values, it´s has to go back on the first sheet and delete.

The sheet it´s like this, if the value on the G2 it´s (Manter a linha), it´s get the number of the row on the F2, and goes to delete the previews of the row. Else, goes to I2, and do the same. Thank you for your help and time.

Sheet

I have this so far:

Sub Delete() 

    Range("G2").Select
    Do Until IsEmpty(ActiveCell)
    Range("G" & Rows.Count).Select
    If Range("G" & 2).Value = ("<<<Manter a linha") Then
    Sheets("Controle Estoque Fixo").Select
    Rows("2:5").Select
    Selection.EntireRow.Delete
    End If
    Loop

EDIT:

Dim r1 As Range, c As Range
    Dim s As String
    Dim v As String
    Dim k As String
    Dim t As String
    k = "1"
    Set r1 = Range(Cells(2, "H"), Cells(Rows.Count, "H").End(xlUp))
    v = Sheets("Analise de Estoque").Cells(2, "G").Value
    For Each c In r1
        If c.Text = ("<<<Manter a linha") Then
        Sheets("Controle Estoque Fixo").Select
        t = (v - 1)

       Rows(t).Select.Clear


        End If
    Next
End Sub

Now I can go back and select the value of the cell that contains the row, that I want to keep, so I add a "- 1" to select before that, but I tried to add the begging and won´t work(tried to add T as a string and put = 1)

Did you read the description of the `macro` tag? *Not for MS Office / VBA / macro langauges*. Removed. — trincot, Jun 26 '16 at 12:52
Hi! You need to introduce variables as row numbers so you have something to loop over. Then after your `If` test, either use the cell value of `"F" & i` or `"H" & i` (where `i` is you current row number) and do the deletion on the other sheet. Don't forget to increment the row number. Be careful, as your row numbers further down will change when you delete rows. Perhaps `.Clear` or `.ClearContents` would be more appropriate. [Here](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) are some tips on how to avoid `Select` and `ActiveCell` — arcadeprecinct, Jun 26 '16 at 13:12

score 1 · Answer 1 · 2016-06-26T19:23:31.920

1

You need to build your range and delete all the rows at once.

Sub DeleteMatches()
    Dim r1 As Range, c As Range
    Dim s As String

    Set r1 = Range(Cells(2, "G"), Cells(Rows.Count, "G").End(xlUp))

    For Each c In r1
        If c = "<<<Manter a linha" Then
            If Len(s) Then s = s & ","
            s = s & "A" & c.Offset(0, -1)
        End If
    Next

    If Len(s) Then
        s = Left(s, Len(s) - 1)
        Sheets("Controle Estoque Fixo").Range(s).EntireRow.Delete
    End If

End Sub

If you only want to clear the rows and not delete then them then you can do it your way.

Sub DeleteMatches2()
    Dim r1 As Range, c As Range
    Dim t As String

    With Sheets("Analise de Estoque")
        Set r1 = .Range(.Cells(2, "H"), .Cells(Rows.Count, "H").End(xlUp))
    End With

    For Each c In r1
        If c.Text = "<<<Manter a linha" Then
            Sheets("Controle Estoque Fixo").Select
            t = c.Offset(0, -1)
            Rows(t).ClearContents
        End If
    Next
End Sub

Sub DeleteMatches3()
    Dim r1 As Range, c As Range
    Dim i As Long, LastRow As Long
    Dim t As String

    With Sheets("Analise de Estoque")
        LastRow = .Cells(Rows.Count, "H").End(xlUp)
        For i = 2 To LastRow
            If .Cells(i, "G").Text = "<<<Manter a linha" Then
                t = .Cells(i, "F").Text
                Sheets("Controle Estoque Fixo").Rows(t).ClearContents
            End If
        Next

    End With
End Sub

Just remember that when you delete rows you have to go from the last row to the first

    For i = LastRow To 2 Step - 1

    Next

edited Jun 26 '16 at 19:23

answered Jun 26 '16 at 13:55

1

you could avoid the first "If-Then" statement ("c" occurrences) using "s = s & "A" & c.Offset(0, -1) & ","" and adding "s = Left(s, Len(s) - 1)" (one occurrence) in the last "If-Then" block – user3598756 Jun 26 '16 at 14:53
I almost got it, the problem now, it´s tha i can select last row, but I couldn´t difine the begginig of the interval. Dim r1 As Range, c As Range Dim s As String Dim v As String Dim k As String k = "1" Set r1 = Range(Cells(2, "H"), Cells(Rows.Count, "H").End(xlUp)) v = Sheets("Analise de Estoque").Cells(2, "G").Value For Each c In r1 If c.Text = ("<< – TerrorJapones Jun 26 '16 at 19:00
The first sub I tried, but it´s deleting the info that I wanna keep. It´s following the logic, but the information that it needs to stay on the sheet is gone and what I want to clear stays on the sheet. I tried to move instead of clearing, but only moves the last row – TerrorJapones Jun 26 '16 at 21:39
Are you trying to delete the row or clear the contents of the row. Changing **.Delete** to **.ClearContents** will do this. Or are you trying to do something else? – Jun 26 '16 at 23:12
Clearcontents, but its clearing the contents that I wanna keep. – TerrorJapones Jun 27 '16 at 08:27
The problem is that its doing what I want, bus its deleting the thing that I wanna keep, I tried to to move those rows that this macro is clearing, but, only moves the last row. – TerrorJapones Jun 27 '16 at 11:55
Ultima coluna do Produto translates to Last Product Column. Are you trying to clear the products that occur before Line to Hold (Manter a linha)? If so do you just clear all the previous rows, or just the rows with matching product names? – Jun 27 '16 at 12:10
it is clear all the previous rows. i am going to try. – TerrorJapones Jun 27 '16 at 14:02

Macro that Delete a Row on a Sheet based on another Sheet

1 Answers1