Cut instead of Copy
 

I have this code here where it finds and copies the entire row where
the search is made.

But instead of copying the entire row is there a way to cut the
information instead?
Let me know thanks

Dim wksToSearch As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String
Dim wksDestination As Worksheet
Dim rngDestination As Range

Set wksDestination = Sheets("Output")
Set rngDestination = wksDestination.Range("A1")
Set wksToSearch = Sheets("Master Questions")
Set rngToSearch = wksToSearch.Columns("E:F")
Set rngFound = rngToSearch.Find(What:="AC", _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry. Not found"

Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
'rngFoundAll.Copy rngDestination

'rngDestination.Resize(rngFoundAll.Cells.Count).In terior.ColorIndex = 3
rngFoundAll.EntireRow.Copy rngDestination
End If

Cut instead of Copy
 

Have you tried changing

rngFoundAll.EntireRow.Copy rngDestination

to

rngFoundAll.EntireRow.Cut rngDestination

--
Regards,
Tom Ogilvy


"RigasMinho" wrote:

I have this code here where it finds and copies the entire row where
the search is made.

But instead of copying the entire row is there a way to cut the
information instead?
Let me know thanks

Dim wksToSearch As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String
Dim wksDestination As Worksheet
Dim rngDestination As Range

Set wksDestination = Sheets("Output")
Set rngDestination = wksDestination.Range("A1")
Set wksToSearch = Sheets("Master Questions")
Set rngToSearch = wksToSearch.Columns("E:F")
Set rngFound = rngToSearch.Find(What:="AC", _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry. Not found"

Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
'rngFoundAll.Copy rngDestination

'rngDestination.Resize(rngFoundAll.Cells.Count).In terior.ColorIndex = 3
rngFoundAll.EntireRow.Copy rngDestination
End If

Cut instead of Copy
 

Yeah i tried that already and it gives me an error.

I'm able to use the cut on this other code i made but that code is
really hard to understand lol.


Tom Ogilvy wrote:
Have you tried changing

rngFoundAll.EntireRow.Copy rngDestination

to

rngFoundAll.EntireRow.Cut rngDestination

--
Regards,
Tom Ogilvy


"RigasMinho" wrote:

I have this code here where it finds and copies the entire row where
the search is made.

But instead of copying the entire row is there a way to cut the
information instead?
Let me know thanks

Dim wksToSearch As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String
Dim wksDestination As Worksheet
Dim rngDestination As Range

Set wksDestination = Sheets("Output")
Set rngDestination = wksDestination.Range("A1")
Set wksToSearch = Sheets("Master Questions")
Set rngToSearch = wksToSearch.Columns("E:F")
Set rngFound = rngToSearch.Find(What:="AC", _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry. Not found"

Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
'rngFoundAll.Copy rngDestination

'rngDestination.Resize(rngFoundAll.Cells.Count).In terior.ColorIndex = 3
rngFoundAll.EntireRow.Copy rngDestination
End If

Cut instead of Copy
 

Yeah i tried that already and it gives me an error.

I'm able to use the cut on this other code i made but that code is
really hard to understand lol.


Tom Ogilvy wrote:
Have you tried changing

rngFoundAll.EntireRow.Copy rngDestination

to

rngFoundAll.EntireRow.Cut rngDestination

--
Regards,
Tom Ogilvy


"RigasMinho" wrote:

I have this code here where it finds and copies the entire row where
the search is made.

But instead of copying the entire row is there a way to cut the
information instead?
Let me know thanks

Dim wksToSearch As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String
Dim wksDestination As Worksheet
Dim rngDestination As Range

Set wksDestination = Sheets("Output")
Set rngDestination = wksDestination.Range("A1")
Set wksToSearch = Sheets("Master Questions")
Set rngToSearch = wksToSearch.Columns("E:F")
Set rngFound = rngToSearch.Find(What:="AC", _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry. Not found"

Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
'rngFoundAll.Copy rngDestination

'rngDestination.Resize(rngFoundAll.Cells.Count).In terior.ColorIndex = 3
rngFoundAll.EntireRow.Copy rngDestination
End If

Cut instead of Copy
 

How about just using .copy and then delete those original rows?

rngFoundAll.EntireRow.Copy rngDestination
rngfoundall.entirerow.delete




RigasMinho wrote:

I have this code here where it finds and copies the entire row where
the search is made.

But instead of copying the entire row is there a way to cut the
information instead?
Let me know thanks

Dim wksToSearch As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String
Dim wksDestination As Worksheet
Dim rngDestination As Range

Set wksDestination = Sheets("Output")
Set rngDestination = wksDestination.Range("A1")
Set wksToSearch = Sheets("Master Questions")
Set rngToSearch = wksToSearch.Columns("E:F")
Set rngFound = rngToSearch.Find(What:="AC", _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry. Not found"

Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
'rngFoundAll.Copy rngDestination

'rngDestination.Resize(rngFoundAll.Cells.Count).In terior.ColorIndex = 3
rngFoundAll.EntireRow.Copy rngDestination
End If

--

Dave Peterson

Cut instead of Copy
 

Heheh thought of that too

But at the end i will use that command line if i have too.

but currently i dont want to do it that way.

i guess i'm just bored so i want to try to figure this out.

I also tried putting the cut line inside the loop. That cuts it over
but craps out and asks to be debugged.

Thanks though for your suggestion

Dave Peterson wrote:
How about just using .copy and then delete those original rows?

rngFoundAll.EntireRow.Copy rngDestination
rngfoundall.entirerow.delete




RigasMinho wrote:

I have this code here where it finds and copies the entire row where
the search is made.

But instead of copying the entire row is there a way to cut the
information instead?
Let me know thanks

Dim wksToSearch As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String
Dim wksDestination As Worksheet
Dim rngDestination As Range

Set wksDestination = Sheets("Output")
Set rngDestination = wksDestination.Range("A1")
Set wksToSearch = Sheets("Master Questions")
Set rngToSearch = wksToSearch.Columns("E:F")
Set rngFound = rngToSearch.Find(What:="AC", _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry. Not found"

Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
'rngFoundAll.Copy rngDestination

'rngDestination.Resize(rngFoundAll.Cells.Count).In terior.ColorIndex = 3
rngFoundAll.EntireRow.Copy rngDestination
End If

--

Dave Peterson

Cut instead of Copy
 

I didn't examine your code closely enough. To the best of my knowledge, you
can't CUT a discontiguous range. Just to confirm:

From VBA help on the CUT method:

The cut range must be made up of adjacent cells.

--
Regards,
Tom Ogilvy




"RigasMinho" wrote:

Yeah i tried that already and it gives me an error.

I'm able to use the cut on this other code i made but that code is
really hard to understand lol.


Tom Ogilvy wrote:
Have you tried changing

rngFoundAll.EntireRow.Copy rngDestination

to

rngFoundAll.EntireRow.Cut rngDestination

--
Regards,
Tom Ogilvy


"RigasMinho" wrote:

I have this code here where it finds and copies the entire row where
the search is made.

But instead of copying the entire row is there a way to cut the
information instead?
Let me know thanks

Dim wksToSearch As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String
Dim wksDestination As Worksheet
Dim rngDestination As Range

Set wksDestination = Sheets("Output")
Set rngDestination = wksDestination.Range("A1")
Set wksToSearch = Sheets("Master Questions")
Set rngToSearch = wksToSearch.Columns("E:F")
Set rngFound = rngToSearch.Find(What:="AC", _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry. Not found"

Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
'rngFoundAll.Copy rngDestination

'rngDestination.Resize(rngFoundAll.Cells.Count).In terior.ColorIndex = 3
rngFoundAll.EntireRow.Copy rngDestination
End If

Cut instead of Copy
 

If you're cutting the line, you'll have to change the way your do/loop works.
You'll never find the first occurence again--it's already been moved.

Dim wksToSearch As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim wksDestination As Worksheet
Dim rngDestination As Range

Set wksDestination = Sheets("Output")
Set rngDestination = wksDestination.Range("A1")
Set wksToSearch = Sheets("Master Questions")
Set rngToSearch = wksToSearch.Columns("E:F")

Do
Set rngFound = rngToSearch.Find(What:="AC", _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)

if rngfound is nothing then
exit do
end if

rngfound.entirerow.cut _
destination:=rngdestination

set rngdestination=rngdestination.offset(1,0)
loop

=====
Untested, uncompiled. Watch for typos.




RigasMinho wrote:

Heheh thought of that too

But at the end i will use that command line if i have too.

but currently i dont want to do it that way.

i guess i'm just bored so i want to try to figure this out.

I also tried putting the cut line inside the loop. That cuts it over
but craps out and asks to be debugged.

Thanks though for your suggestion

Dave Peterson wrote:
How about just using .copy and then delete those original rows?

rngFoundAll.EntireRow.Copy rngDestination
rngfoundall.entirerow.delete




RigasMinho wrote:

I have this code here where it finds and copies the entire row where
the search is made.

But instead of copying the entire row is there a way to cut the
information instead?
Let me know thanks

Dim wksToSearch As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String
Dim wksDestination As Worksheet
Dim rngDestination As Range

Set wksDestination = Sheets("Output")
Set rngDestination = wksDestination.Range("A1")
Set wksToSearch = Sheets("Master Questions")
Set rngToSearch = wksToSearch.Columns("E:F")
Set rngFound = rngToSearch.Find(What:="AC", _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry. Not found"

Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
'rngFoundAll.Copy rngDestination

'rngDestination.Resize(rngFoundAll.Cells.Count).In terior.ColorIndex = 3
rngFoundAll.EntireRow.Copy rngDestination
End If

--

Dave Peterson

--

Dave Peterson