Select all cells that contain the current Date with VBA In column B Find all cel

Question

Select all cells that contain the current Date with VBA

In column B Find all cells that contain the current Date and cut this Rows and Paste into another Range.

I try this code but not work send a error in the line with bold.

LastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row

For Each d In Range("B10:B" & LastRow)

     If d.Value = Date Then
     Rows("B" & d.Row).EntireRow.Cut '<===Error in this line
     Range("A9").Select
    Selection.Insert Shift:=xlDown
    With Selection.Interior
        .Color = 5296274
    End With
    End If
Next d

Thanks for help !!

Explanation / Answer

Hi..

Hi,

Try this on a copy of your data.

Sub somesub()
Dim LastRow As Long, d As Long
Dim CopyRange As Range, count As Long
LastRow = Cells(Cells.Rows.count, "B").End(xlUp).Row
For d = LastRow To 10 Step -1
If IsDate(Cells(d, "B")) And Cells(d, "B").Value = Date Then
count = count + 1
      If CopyRange Is Nothing Then
                Set CopyRange = Rows(d).EntireRow
        Else
                Set CopyRange = Union(CopyRange, Rows(d).EntireRow)
        End If
    End If
Next d
If Not CopyRange Is Nothing Then
    Cells(9, "A").Resize(count).EntireRow.Insert
    CopyRange.Copy Destination:=Cells(9, "A")
    CopyRange.Delete
    Cells(9, "A").Resize(count).Interior.Color = 5296274
End If
End Sub

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.