We help IT Professionals succeed at work.

Excel Macro

I would like a simple macro to delete a row in excel based on the content of a column. I have tried a few different macros from here but none work. The logic is: If column one = "SHARE Digital Collection" Then delete the row. I will modify it based on different values in different columns.
Comment
Watch Question

Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
There are different ways you can do this, below is one in which you loop row by row.

Something like
    Dim lLastRow              As Long

    lLastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

    For i = lLastRow To 1 Step -1    '1 being the starting row to check
        If Cells(i, 1).Value = "SHARE Digital Collection" Then Rows(i & ":" & i).Delete Shift:=xlUp
    Next i

Open in new window


This code is checking the value of Column 1 = "A", simply change the 1 in Cells(i, 1).Value to match the column number you want to check for your string to delete based on.
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
If you want to use another possible approach using AutoFilters, look at https://www.mrexcel.com/board/threads/vba-delete-entire-row-if-contains-certain-text.300330/
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:

You may tweak it as per your requirement...


Sub DeleteRows()
Dim ws              As Worksheet
Dim lr              As Long
Dim HeaderRow       As Long
Dim ColumnToFilter  As Long
Dim strCriteria     As String

Application.ScreenUpdating = False

Set ws = Worksheets("Sheet1")   'Setting the Worksheet
lr = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

HeaderRow = 1       'Assuming Row1 is the header row
ColumnToFilter = 1  'Assuming column A is to be filtered

strCriteria = "SHARE Digital Collection"    'Autofilter Criteria

ws.AutoFilterMode = False

With ws.Rows(HeaderRow)
    .AutoFilter field:=ColumnToFilter, Criteria1:=strCriteria
    If ws.Range(ws.Cells(HeaderRow, ColumnToFilter), ws.Cells(lr, ColumnToFilter)).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
        ws.Range(ws.Cells(HeaderRow + 1, ColumnToFilter), ws.Cells(lr, ColumnToFilter)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End If
    .AutoFilter
End With
Application.ScreenUpdating = True
End Sub
Commented:
Sub delShare()
    Set dr = ActiveSheet.UsedRange
        For i = dr.Row + dr.Rows.Count To dr.Row Step -1
            If Cells(i, 1) = "SHARE Digital Collection" Then
            Cells(i, 1).EntireRow.Delete
            End If
        Next i
           
End Sub