0

I have data sheets that vary in size week to week. I want to delete rows which contain the string "CHF" which is in column D, but again, column D changes in size each week. I don't even know where to start with this. I've looked at the similar questions recommended when writing this question but still have not figured it out. Any solutions?

NWL
  • 11
  • 3

2 Answers2

1

Delete Rows (For...Next Loop feat. Union)

  • Adjust the values in the constants section.
Option Explicit

Sub deleteRows()
    
    ' Define constants.
    Const wsName As String = "Sheet1"
    Const cFirst As Long = 2
    Const cCol As String = "D"
    Const Crit As String = "CHF"
    
    ' Define workbook, worksheet, and last row.
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)
    Dim cLast As Long: cLast = ws.Cells(ws.Rows.Count, cCol).End(xlUp).Row
    
    ' Combine cells ('cCell') containing Criteria into Delete Range ('drg').
    Dim drg As Range
    Dim cCell As Range
    Dim i As Long
    For i = cFirst To cLast
        Set cCell = ws.Cells(i, cCol)
        If cCell.Value = Crit Then
            If drg Is Nothing Then
                Set drg = cCell
            Else
                Set drg = Union(drg, cCell)
            End If
        End If
    Next i
    
    ' Delete entire rows of Delete Range in one go.
    If Not drg Is Nothing Then
        drg.EntireRow.Delete
    End If
    
End Sub
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
0

This is the basic premise of Filter and Delete mentioned earlier by @BigBen

Sub FilterDelete()
    
    Application.DisplayAlerts = False
    
    Dim rng As Range
    Dim LR as Long
    LR=Worksheets("Sheet1").Cells(Rows.Count,1).End(xlUp).Row

    Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1:H"&LR)
    
    rng.AutoFilter Field:=4, Criteria1:="CHF"
    rng.Offset(1,0).SpecialCells(xlCellTypeVisible).Delete
    
    AutoFilterMode = False
    
    Application.DisplayAlerts = True
    
    
End Sub

You may have more or less columns. You can use a similar method to find the last column. Not seeing your data, I am giving an example.

Darrell H
  • 1,876
  • 1
  • 9
  • 14
  • For this, what if I have more than 500 rows? I assume A1:H500 means it stops at H500. The data I have can be anywhere from a few thousand rows, to the most I've seen of 15,000 rows. But one day it may be even bigger than that. – NWL Mar 19 '21 at 19:36
  • Meant to display the functionality of filter and delete. I can add finding the last row. – Darrell H Mar 19 '21 at 19:38
  • If you don't mind showing how to do that, I would appreciate it! – NWL Mar 19 '21 at 19:43
  • `LC = Worksheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column`. You would also want to declare as `Long`. This assumes your header or data is in row 1. – Darrell H Mar 19 '21 at 19:46
  • Awesome, thank you! – NWL Mar 19 '21 at 20:14