Check if range meets criteria if its not in criteria copy to different column

Hi,

I am trying to create a macro that check whether a range has dates in it and if the values aren't dates then copy those values accross to to a different column but the same row.

heres my code so far, I am stuck on how to copy accross

Sub Copy()'
' Copy Macro
'




'




    Dim dtRng As Range
    Dim dtrow As Integer
    
    Set dtRng = Sheet1.Range("C16:C" & Sheet1.Cells(Rows.Count, 1).End(xlUp).Row)
    
        For dtrow = 2 To dtRng.Count + 1
        If Not IsDate(dtRng.Value) Then
            Sheet1.Cells(dtrow).Copy destination:=
            
            




End Sub

Re: Check if range meets criteria if its not in criteria copy to different column

Pangolin

That worked for me as well.

Thank you very much

Re: Check if range meets criteria if its not in criteria copy to different column

Hi pangolin

if possible, I also want it to copy the corresponding range rows to a different worksheet

Sub nexts()


    Dim dtRng As Range
    
        With Sheet1
            Set dtRng = .Range("C16", .Range("C" & .Rows.Count).End(xlUp))
        End With




    Dim myCell As Range
        For Each myCell In dtRng
             x = x + 1
            If x >= 2 Then
                If Not IsDate(myCell.Value) Then
                    Sheet1.Range("A" & myCell.Row) = myCell.Value
                End If
            End If
        Next myCell
End Sub

Re: Check if range meets criteria if its not in criteria copy to different column

change it to this?

Sheet1.Range(myCell.Row).Copy Destination:=Sheets("calc")

Re: Check if range meets criteria if its not in criteria copy to different column

Hi

I changed it to this:

myCell.EntireRow.Copy Destination:=Sheets("calc").Range("A" & Rows.Count).End(xlUp).Offset(1)

sorry this loops copying and pasting in the second row of the the calc sheet

Re: Check if range meets criteria if its not in criteria copy to different column

Its still the same error

Re: Check if range meets criteria if its not in criteria copy to different column

Yes that worked, but how do i make it paste without any spaces?

Re: Check if range meets criteria if its not in criteria copy to different column

Pangolin, thanks again. ended up working out the empty cell things myself.

but very much appreciated