Nsryjdtyk

I am using below VBA code to fetch all rows if date is greater than 10/31/2013 but my code is not working. My code is working fine if i am usine date = 10/31/2013. Can anyone help me why my code is not working for greater than 10/31/2013 condition.My date column also has null values.

Private Sub CommandButton21_Click()



a = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row



For i = 1 To a



    If Worksheets("Sheet1").Cells(i, 7).Value > "10/31/2013" Then



        Worksheets("Sheet1").Rows(i).Copy

        Worksheets("Sheet2").Activate

        b = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row

        Worksheets("Sheet2").Cells(b + 1, 1).Select

        ActiveSheet.Paste

        Worksheets("Sheet1").Activate



    End If



Next



Application.CutCopyMode = False

ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Select



End Sub

You are not properly qualifying your objects with a sheet which could very well be the issue. I adapted your code to properly qualify all objects & this will also be much faster since it will only copy/paste outside of the loop once.

For instance, say you have 500 rows that meet your criteria ( Range > Date). This means you will have 500 instances of rows being copied and pasted over and over inside your loop. The below method will only have one instance of copy/paste and is independent of how many rows meet your criteria. The more rows to be copied, the more you will benefit from this solution.

Another possible solution is to just simply filter by your criteria and copy/paste the visible cells only

Updated for more criteria added in comments - tested and working fine on my end

Option Explicit



Private Sub CommandButton21_Click()



Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")

Dim i As Long, MyUnion As Range, LRow As Long



For i = 2 To ws.Range("A" & ws.Rows.Count).End(xlUp).Row

    If ws.Range("G" & i) > #10/31/2013# Or ws.Range("AA" & i) = "Investigate" Or ws.Range("AA" & i) = "Leave Open" Then

        If Not MyUnion Is Nothing Then

            Set MyUnion = Union(MyUnion, ws.Range("G" & i))

        Else

            Set MyUnion = ws.Range("G" & i)

        End If

    End If

Next i



If Not MyUnion Is Nothing Then

    With ThisWorkbook.Sheets("Sheet2")

        LRow = .Range("A" & .Rows.Count).End(xlUp).Offset(1).Row

        MyUnion.EntireRow.Copy .Range("A" & LRow)

    End With

End If



End Sub

Before & After

Dates are numeric values. Use # instead of " when working with dates.

Selecting or activating ranges should be avoided watch Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset).

Private Sub CommandButton21_Click()

    Application.ScreenUpdating = False

    Dim r As Long



    With ThisWorkbook.Worksheets("Sheet1")

        For r = 1 To .Cells(Rows.Count, 1).End(xlUp).Row

            If .Cells(r, 7).Value > #10/31/2013# Then

                .Rows(r).Copy Destination:=Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1)

            End If

        Next

    End With



    Application.CutCopyMode = False



End Sub

The reason your > comparison is not working as you would expect is because you have provided a string (wrapped in ") in your comparison. If you want to compare dates, provide a date wrapped in # like this:

If CDate(Worksheets("Sheet1").Cells(i, 7).Value) > #10/31/2013# Then

Note that I've also ensured the value in your cell is converted to a Date datatype by using CDate

Some additional comments, not related directly to your question:

You use .select and .activate in your code, but it is a much better practice to interact with the objects directly. For example, that whole If Block should be more like this:

If CDate(Worksheets("Sheet1").Cells(i, 7).Value) > #10/31/2013# Then

    Worksheets("Sheet1").Rows(i).Copy

    b = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row

    Worksheets("Sheet2").Cells(b + 1, 1).Paste

End If