-1

SO community. I have a problem that I apparently can't solve on my own. This is for self educational purpose only regarding school work. So, I have multiple columns, which contain different value types. In example below there are 3 columns:

  • column 1 "Date" - date;
  • column 2 "ID" - number;
  • column 3 "Letter" - text.

What I am trying to do - based on "Date" and "Letter" columns delete a row that is older than, for example, 29.06.2015 and at the same time the date has a letter "A" assigned. So, basically if date is older than 29.06.2015 and that row contains letter "A", it gets deleted, if not - it stays as it is.

Date        ID  Letter
12.01.2013  1   A
05.02.2014  2   A
29.06.2015  3   A
04.01.2016  4   A
17.12.2014  5   A
12.01.2013  6   B
05.02.2014  7   B
29.06.2015  8   A
04.01.2016  9   F
17.12.2014  10  F

So the final result should look like this:

Date    ID  Letter
29.06.2015  3   A
04.01.2016  4   A
12.01.2013  6   B
05.02.2014  7   B
29.06.2015  8   A
04.01.2016  9   F
17.12.2014  10  F

I have been searching for a solution to this problem and found articles like this, and this and few others, but they are focused on slightly different things as I can't really wrap my head around to what to do.

Jamycle
  • 29
  • 3
  • Can you provide what you have tried so far? This is easily achievable with **VBA**. To give you pointers: when deleting rows with VBA, always start from the bottom. Try not working with sheet directly but instead, capture your range in an array.. manipulate the array and then update your range on the sheet – Zac Sep 17 '18 at 12:18

1 Answers1

1

Try this code

Option Explicit

Sub DeleteOld(ByVal MinDate As Date)
    Dim i As Integer
    i = 2
    Do While CStr(ActiveSheet.Cells(i, 1).Value) <> "" And CStr(ActiveSheet.Cells(i, 2).Value) <> "" And CStr(ActiveSheet.Cells(i, 3).Value) <> ""
        If DateDiff("d", CDate(ActiveSheet.Cells(i, 1).Value), MinDate) > 0 And CStr(ActiveSheet.Cells(i, 3).Value) = "A" Then
            ActiveSheet.Rows(i).EntireRow.Delete
        Else
            i = i + 1
        End If
    Loop
End Sub

Sub TestDeleteOld()
    Call DeleteOld(DateSerial(2015, 5, 29))
End Sub
Terminader
  • 72
  • 5
  • I like the Do -> While loop, I always use For and subtract one from i if found. That's a much better way :D – seadoggie01 Sep 17 '18 at 13:16
  • Hmm. It gives me ''type mismatch'' error. Does it have to do something with cell formatting? – Jamycle Sep 17 '18 at 13:17
  • I think it's due to the date formatting. For my test I have the data in columns A,B and C. The first row of data is 2 (The first riw is for the headers) – Terminader Sep 17 '18 at 13:26