Hello:

I have been working on this all day and have the macro doing exactly
the opposite of what I want.

I have a workbook with 2 sheets. All data will start on sheet 1(In
Progress). I want to run a macro to move the rows that have any data
in col E to sheet 2 (Completed).

I have had problems making the selection of NOT EMPTY happen.

This is what I have - that works backwards!

' to copy completed items to completed sheet and delete from In
Progress

Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim rngPaste As Range
Dim strFirstAddress As String
Set rngPaste = Sheets("Completed").Cells(Rows.Count, _
"A").End(xlUp).Offset(1, 0)
Set rngToSearch = ActiveSheet.Columns("E")

Set rngFound = rngToSearch.Find(What:="", _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=True)

If rngFound Is Nothing Then
MsgBox "There are no items to move."
Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
rngFoundAll.EntireRow.Copy Destination:=rngPaste
rngFoundAll.EntireRow.Delete 'Optional to Delete
End If

Any help would be greatly appreciated!

Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim rngPaste As Range
Dim strFirstAddress As String
Set rngPaste = Sheets("Completed").Cells(Rows.Count, _
"A").End(xlUp).Offset(1, 0)

sheets("Progress").activate '<= Add

Set rngToSearch = ActiveSheet.Columns("E")

Set rngFound = rngToSearch.Find(What:="", _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=True)

If rngFound Is Nothing Then
MsgBox "There are no items to move."
Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
rngFoundAll.EntireRow.Copy Destination:=rngPaste
rngFoundAll.EntireRow.Delete 'Optional to Delete
End If


"JenIT" wrote:

> Hello:
>
> I have been working on this all day and have the macro doing exactly
> the opposite of what I want.
>
> I have a workbook with 2 sheets. All data will start on sheet 1(In
> Progress). I want to run a macro to move the rows that have any data
> in col E to sheet 2 (Completed).
>
> I have had problems making the selection of NOT EMPTY happen.
>
> This is what I have - that works backwards!
>
> ' to copy completed items to completed sheet and delete from In
> Progress
>
> Dim rngToSearch As Range
> Dim rngFound As Range
> Dim rngFoundAll As Range
> Dim rngPaste As Range
> Dim strFirstAddress As String
> Set rngPaste = Sheets("Completed").Cells(Rows.Count, _
> "A").End(xlUp).Offset(1, 0)
> Set rngToSearch = ActiveSheet.Columns("E")
>
> Set rngFound = rngToSearch.Find(What:="", _
> LookIn:=xlFormulas, _
> LookAt:=xlWhole, _
> MatchCase:=True)
>
> If rngFound Is Nothing Then
> MsgBox "There are no items to move."
> Else
> Set rngFoundAll = rngFound
> strFirstAddress = rngFound.Address
> Do
> Set rngFoundAll = Union(rngFound, rngFoundAll)
> Set rngFound = rngToSearch.FindNext(rngFound)
> Loop Until rngFound.Address = strFirstAddress
> rngFoundAll.EntireRow.Copy Destination:=rngPaste
> rngFoundAll.EntireRow.Delete 'Optional to Delete
> End If
>
> Any help would be greatly appreciated!
>
>

I can't understand why you are searching blank cell and copy it.

Set rngFound = rngToSearch.Find(What:="", _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=True)

Is What:="" typo of What:="*" ? but not sure.

keizi

"JenIT" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hello:
>
> I have been working on this all day and have the macro doing exactly
> the opposite of what I want.
>
> I have a workbook with 2 sheets. All data will start on sheet 1(In
> Progress). I want to run a macro to move the rows that have any data
> in col E to sheet 2 (Completed).
>
> I have had problems making the selection of NOT EMPTY happen.
>
> This is what I have - that works backwards!
>
> ' to copy completed items to completed sheet and delete from In
> Progress
>
> Dim rngToSearch As Range
> Dim rngFound As Range
> Dim rngFoundAll As Range
> Dim rngPaste As Range
> Dim strFirstAddress As String
> Set rngPaste = Sheets("Completed").Cells(Rows.Count, _
> "A").End(xlUp).Offset(1, 0)
> Set rngToSearch = ActiveSheet.Columns("E")
>
> Set rngFound = rngToSearch.Find(What:="", _
> LookIn:=xlFormulas, _
> LookAt:=xlWhole, _
> MatchCase:=True)
>
> If rngFound Is Nothing Then
> MsgBox "There are no items to move."
> Else
> Set rngFoundAll = rngFound
> strFirstAddress = rngFound.Address
> Do
> Set rngFoundAll = Union(rngFound, rngFoundAll)
> Set rngFound = rngToSearch.FindNext(rngFound)
> Loop Until rngFound.Address = strFirstAddress
> rngFoundAll.EntireRow.Copy Destination:=rngPaste
> rngFoundAll.EntireRow.Delete 'Optional to Delete
> End If
>
> Any help would be greatly appreciated!
>

Thanks to all for your help the "*" was what I needed.

As always this group is a super resouce for a VBA dabbler such as
myself. I appreciate the assistance.