Clear Contents in Sheet1 based on a date in Sheet2

Question

Short question. I have one excel file with two sheets (Sheet1, Sheet2). On sheet1 column A i have a list of dates, on column D i have some numbers. On sheet2 A16, i have a date.

I would like to be able to delete the numbers on Sheet1 column D, starting from a date found in sheet2 cell A16. The date in Sheet2!A16 will change everyday, so i will delete numbers starting from different dates...

sheet 1 image

sheet 2 image

pwwolff pwwolff · Accepted Answer · 2017-10-14T20:02:32

Try something along these lines:

Sub UpdateColD()
  Dim ws1 As Worksheet
  Dim strDate As String
  Dim lngLastRow As Long
  Dim lngDateRow As Long

  Set ws1 = ActiveWorkbook.Worksheets("Sheet1")

  'Find the last row in column D - we will clear all cells until and including this row
  lngLastRow = ws1.Range("D" & Rows.Count).End(xlUp).Row

  ' Get Date - careful with formatting and types, I've used a string
  strDate = ActiveWorkbook.Worksheets("Sheet2").Cells(16, 1)

  ' Iterate over all rows until you find the value
  For r = 1 To lngLastRow
    If ws1.Cells(r, 1) = strDate Then
        lngDateRow = r
        Exit For
    End If
  Next r

  ' Only clear if the date was found
  If lngDateRow > 0 Then
      ws1.Range(ws1.Cells(lngDateRow + 1, 4), ws1.Cells(lngLastRow, 4)).Clear
  End If
End Sub

Clear Contents in Sheet1 based on a date in Sheet2

1 Answers