Forum Discussion

clh_1496's avatar
clh_1496
Brass Contributor
Jun 07, 2022

Delete rows based on value in different sheet

Hi,

 

How would I reference Sheet called Summary, cells G3 and J3 below so that it deletes rows with dates less the G3 and greater than J3? I can't seem to get it to look up the date in these cells to then delete rows from? And is it possible to delete cells based on time and date, for example, if cell U is before summary G3 AND cell V before summary G4?

 

lr = Cells(Rows.Count, "U").End(xlUp).Row 'find last row
For i = lr To 2 Step -1 'loop thru backwards, finish at 2 for headers
If Cells(i, "U").Value < "=Summary!G3" Then Rows(i).EntireRow.Delete
If Cells(i, "U").Value > "=Summary!J3" Then Rows(i).EntireRow.Delete
Next i

excel
Formulas and Functions
Macros and VBA
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jun 07, 2022

    clh_1496 

    Sub summary()

Dim lr As Integer
Dim i As Integer

lr = Cells(Rows.Count, "U").End(xlUp).Row 'find last row

For i = lr To 2 Step -1 'loop thru backwards, finish at 2 for headers

'column "U" is column21

If Cells(i, 21).Value < Worksheets("Summary").Cells(3, 7).Value Or Cells(i, 21).Value > Worksheets("Summary").Cells(3, 10).Value Then
Rows(i).EntireRow.Delete

Else

End If

Next i

End Sub

    Maybe with these lines of code. In the attached file you can click the button in cell R1 to start the macro.

    • clh_1496's avatar
      clh_1496
      Brass Contributor
      Jun 08, 2022

      OliverScheurich 

       

      Is it possible to add an AND so that it also looks at the date in col U and also the time in col V and if both are true then deletes the column? I tried to do it below and whilst it doesn't come up with an error, it doesn't seem to delete anything?

       

      lr = Cells(Rows.Count, "U").End(xlUp).Row 'find last row
      lr = Cells(Rows.Count, "V").End(xlUp).Row 'find last row
      For i = lr To 2 Step -1 'loop thru backwards, finish at 2 for headers
      'column "U" is column21
      If Cells(i, 21).Value < Worksheets("Summary").Cells(3, 7).Value And Cells(i, 22).Value < Worksheets("Reference Sheet").Cells(12, 4).Value Or Cells(i, 21).Value > Worksheets("Reference Sheet").Cells(4, 4).Value And Cells(i, 22).Value > Worksheets("Reference Sheet").Cells(13, 4).Value Then
      Rows(i).EntireRow.Delete

      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        Jun 08, 2022

        clh_1496 

        Sub summaryandreference()

Dim lrU As Integer
Dim LrV As Integer
Dim i As Integer

lrU = Cells(Rows.Count, "U").End(xlUp).Row 'find last row
LrV = Cells(Rows.Count, "V").End(xlUp).Row 'find last row
For i = lrU To 2 Step -1 'loop thru backwards, finish at 2 for headers
'column "U" is column21
If Cells(i, 21).Value < Worksheets("Summary").Cells(3, 7).Value And _
Cells(i, 22).Value < Worksheets("Reference Sheet").Cells(12, 4).Value Or _
Cells(i, 21).Value > Worksheets("Summary").Cells(3, 10).Value And _
Cells(i, 22).Value > Worksheets("Reference Sheet").Cells(13, 4).Value Then
Rows(i).EntireRow.Delete
Else
End If
Next i


End Sub

        Maybe with this code. You can click the button in cell P1 in the attached file to start the macro.

Resources