-1

I am attempting to combine data from multiple tables on one sheet into one comprehensive table, where every column from each individual table is sorted by column "DATE". How can I use VBA to automatically delete any rows that do not contain the word "DATE" or numbers "1-31"?

I am completely new to VBA code. This is something I was tasked with at my job. I've done lots of googling but have been unable to find any code that does what I need.

Asger
  • 3,822
  • 3
  • 12
  • 37
sine_0
  • 1
  • 1
    Welcome to SO. Please read [How to ask](https://stackoverflow.com/help/how-to-ask) guidelines and adjust your question accordingly. People may help you with specific doubts, but does not expect anyone to do your work for you. – GCSDC May 11 '19 at 22:14
  • `If [Range].EntireRow.Find("DATE", LookIn:=xlValues) Is Nothing Then [Range].EntireRow.Delete` is a start. `.Find` will be cumbersome with 32 criterias though. Maybe you could get some ideas from [this question](https://stackoverflow.com/questions/20764851/vba-excel-find-based-on-multiple-search-criteria-without-looping). – Christofer Weber May 12 '19 at 02:01
  • Use a filter, either the regular or advanced. – Ron Rosenfeld May 12 '19 at 10:24

1 Answers1

0

I would do something like this. This assumes the data to check is in Column A and B this will loop through and clear the cell content if matches your criteria and then it maps the empty rows to delete all in one go.

Sub DelRows()

    Dim ws As Worksheet
    Dim delRange As Range
    Dim lrow As Long, i As Long

    Set ws = ActiveSheet

    With ws
        lrow = .Range("A" & .Rows.Count).End(xlUp).Row

        '--> Delete All rows where Cell A and Cell B are empty
        For i = 2 To lrow

            If .Range("A" & i).value = "DATE" Or .Range("B" & i).value = "1-31" Then
                .Range("A" & i).value = ""
                .Range("B" & i).value = ""
            End If

            If Len(Trim(.Range("A" & i).value)) = 0 Or Len(Trim(.Range("B" & i).value)) = 0 Then
                If delRange Is Nothing Then
                    Set delRange = .Rows(i)
                Else
                    Set delRange = Union(delRange, .Rows(i))
                End If
            End If
        Next i

        If Not delRange Is Nothing Then delRange.Delete

        Set delRange = Nothing

End Sub
QuickSilver
  • 730
  • 5
  • 28