How to delete a row on a set template using VBA?

Question

Newbie to VBA.

I have a simple excel template that generates summarized data from a source file. In other words, I click a button and the numbers are refreshed on this template on one of the tabs.

On this template, however, there is a row that I no longer need. I want to remove it completely but I cannot just delete it. Let's say this is 'Cost of Goods Sold' (see code), and I don't need this anymore. The number attached to COGS is now 0 because my source file no longer has info abt this.

The entire chunk of code here is for this Cost of Goods Sold row, which I don't understand completely... Again, just want to remove as no longer relevant.

Pls advise what I can do. Much appreciated.

If Range("B" & t) = "Cost of goods sold" Then
    Range("B" & t, "G" & t).Select
    Selection.Copy
    Sheets("Summary").Select
    Range("A" & (rowXListTotal - z + d)).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    d = d + 1
    Worksheets("SP").Select
End If

My first advice is read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and apply this technique to your code befor you do anything else. — Pᴇʜ, Jun 25 '19 at 10:00
Actually *"I want to remove it completely but I cannot just delete it."* This doesn't make any sense. Removing it means you need to delete that line. Maybe a screenshot will help to explain it better. — Pᴇʜ, Jun 25 '19 at 10:03

score 0 · Answer 1 · answered Jun 25 '19 at 10:12

A simple example on how to processed:

Option Explicit

Sub test()

    Dim LastRow As Long, i As Long

    With ThisWorkbook.Worksheets("Sheet1")

        'Find the last row of column A
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        'Loop aiming to delete. Start from bottom to top using Step -1
        For i = LastRow To 1 Step -1

            'If condition made, then delete
            If .Range("B" & i).Value = "DeleteMe" Then
                .Rows(i).EntireRow.Delete
            End If

        Next i

    End With

End Sub

How to delete a row on a set template using VBA?

1 Answers1