Excel VBA

MVX · Oct 23, 2008

Help!!! Am at a loss

How do i write a macro that would look for a string (Numeric e.g. "999") in colum's 'O' - 'T'? (Columns 15 - 20)
If the string is found, i want it (the macro) too copy that entire row to a 'new' sheet (Sheet2) in the same workbook and delete the row from Sheet1?
To explain - On searching column 'O' and if string found; copy row to Sheet2: delete row from Sheet1; and then search column 'P' and so on until data sucessfully copied/deleted??
Difficulty, I have is that the same string can be repeated in different columns and unless the row once copied to 'new' sheet is deleted I would have the same 'row' copied over to Sheet2 from a different column - skewing the resulting data.

Hope this makes sense!

Many Thanks

MVX

VoG · Oct 23, 2008

Hello and welcome to MrExcel.

Try

Code:

Sub test()
Dim LR As Long, Found As Range
Set Found = Sheets("Sheet1").Range("O15:T20").Find(what:=999)
If Not Found Is Nothing Then
    With Sheets("Sheet2")
        LR = Range("A" & Rows.Count).End(xlUp).Row
        Found.EntireRow.Copy Destination:=.Range("A" & LR + 1)
    End With
    Application.CutCopyMode = False
    Found.EntireRow.Delete
End If
End Sub

MVX · Oct 23, 2008

Vog
That was quick!
Many Thanks for the reply - I will run the script - It 'looks' so simple - I have spent HOURS and HOURS agonising over this before posting.
Will let you know how I get on

Regards

Steve

MVX · Oct 23, 2008

Vog
Nope run a trial of the script and would not appear to work - It would appear to ignore the instances of the numeric in Column 'O' and only copies one entry of the numeric from column 'P'
The Raw data has numerous rows and the script would not appear to be working ??

VoG · Oct 23, 2008

It worked for me when I tested with 999 (in separate tests) in several of the cells in your defined range.

Can you post a shot of the relevant (O15:T20) range using Excel Jeanie - for instructions click here.

MVX · Oct 27, 2008

VOG -
Sorry for delay in getting back to you - the data has 43930 rows and will always have a 'string' (numeric) in column 'O': I originally used '999' as an example but as you will see the VBA code could be altered to filter out '114' or '115'. Further, Columns 'P', 'Q', 'R', 'S' and 'T' don't always have a 'string' in the cells.
Any help advice or assistance would be greatfully appreciated

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 138px"><COL style="WIDTH: 75px"><COL style="WIDTH: 75px"><COL style="WIDTH: 75px"><COL style="WIDTH: 75px"><COL style="WIDTH: 75px"><COL style="WIDTH: 75px"><COL style="WIDTH: 93px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>N</TD><TD>O</TD><TD>P</TD><TD>Q</TD><TD>R</TD><TD>S</TD><TD>T</TD><TD>W</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>OFF_ID2</TD><TD>DISPOSE_CO</TD><TD>DISPOSE__1</TD><TD>DISPOSE__2</TD><TD>DISPOSE__3</TD><TD>DISPOSE__4</TD><TD>DISPOSE__5</TD><TD>DATE_CONTR</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">27385.0</TD><TD style="TEXT-ALIGN: right">282</TD><TD style="TEXT-ALIGN: right">995</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">12/10/2007</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">20699.0</TD><TD style="TEXT-ALIGN: right">241</TD><TD style="TEXT-ALIGN: right">105</TD><TD style="TEXT-ALIGN: right">106</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">09/11/2007</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">0.0</TD><TD style="TEXT-ALIGN: right">114</TD><TD style="TEXT-ALIGN: right">641</TD><TD style="TEXT-ALIGN: right">313</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">01/12/2007</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">20873.0</TD><TD style="TEXT-ALIGN: right">663</TD><TD style="TEXT-ALIGN: right">114</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">12/12/2007</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">26324.0</TD><TD style="TEXT-ALIGN: right">281</TD><TD style="TEXT-ALIGN: right">995</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">12/10/2007</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">0.0</TD><TD style="TEXT-ALIGN: right">118</TD><TD style="TEXT-ALIGN: right">106</TD><TD style="TEXT-ALIGN: right">995</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">12/10/2007</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">23695.0</TD><TD style="TEXT-ALIGN: right">411</TD><TD style="TEXT-ALIGN: right">106</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">03/10/2007</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">24176.0</TD><TD style="TEXT-ALIGN: right">103</TD><TD style="TEXT-ALIGN: right">106</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">12/12/2007</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">21217.0</TD><TD style="TEXT-ALIGN: right">103</TD><TD style="TEXT-ALIGN: right">281</TD><TD style="TEXT-ALIGN: right">284</TD><TD style="TEXT-ALIGN: right">111</TD><TD style="TEXT-ALIGN: right">118</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">12/12/2007</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right">15718.0</TD><TD style="TEXT-ALIGN: right">114</TD><TD style="TEXT-ALIGN: right">115</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">10/01/2008</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: right">27574.0</TD><TD style="TEXT-ALIGN: right">103</TD><TD style="TEXT-ALIGN: right">106</TD><TD style="TEXT-ALIGN: right">996</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">22/07/2008</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: right">0.0</TD><TD style="TEXT-ALIGN: right">103</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">22/07/2008</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: right">7942.0</TD><TD style="TEXT-ALIGN: right">114</TD><TD style="TEXT-ALIGN: right">271</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">22/07/2008</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: right">15715.0</TD><TD style="TEXT-ALIGN: right">114</TD><TD style="TEXT-ALIGN: right">115</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">22/07/2008</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: right">18567.0</TD><TD style="TEXT-ALIGN: right">996</TD><TD style="TEXT-ALIGN: right">118</TD><TD style="TEXT-ALIGN: right">105</TD><TD style="TEXT-ALIGN: right">103</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">22/07/2008</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: right">15715.0</TD><TD style="TEXT-ALIGN: right">106</TD><TD style="TEXT-ALIGN: right">118</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">22/07/2008</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="TEXT-ALIGN: right">21461.0</TD><TD style="TEXT-ALIGN: right">114</TD><TD style="TEXT-ALIGN: right">641</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">23/07/2008</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: right">19219.0</TD><TD style="TEXT-ALIGN: right">103</TD><TD style="TEXT-ALIGN: right">118</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">22/07/2008</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: right">21099.0</TD><TD style="TEXT-ALIGN: right">241</TD><TD style="TEXT-ALIGN: right">103</TD><TD style="TEXT-ALIGN: right">106</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">22/07/2008</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD style="TEXT-ALIGN: right">18567.0</TD><TD style="TEXT-ALIGN: right">103</TD><TD style="TEXT-ALIGN: right">996</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">22/07/2008</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD style="TEXT-ALIGN: right">8029.0</TD><TD style="TEXT-ALIGN: right">102</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">22/07/2008</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: right">0.0</TD><TD style="TEXT-ALIGN: right">106</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">17/07/2008</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: right">0.0</TD><TD style="TEXT-ALIGN: right">106</TD><TD style="TEXT-ALIGN: right">118</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">15/10/2007</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD style="TEXT-ALIGN: right">0.0</TD><TD style="TEXT-ALIGN: right">241</TD><TD style="TEXT-ALIGN: right">106</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">17/08/2008</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">26</TD><TD style="TEXT-ALIGN: right">0.0</TD><TD style="TEXT-ALIGN: right">107</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">10/06/2008</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">27</TD><TD style="TEXT-ALIGN: right">21462.0</TD><TD style="TEXT-ALIGN: right">103</TD><TD style="TEXT-ALIGN: right">499</TD><TD style="TEXT-ALIGN: right">106</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">11/06/2008</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">28</TD><TD style="TEXT-ALIGN: right">27861.0</TD><TD style="TEXT-ALIGN: right">106</TD><TD style="TEXT-ALIGN: right">103</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">14/06/2008</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">29</TD><TD style="TEXT-ALIGN: right">0.0</TD><TD style="TEXT-ALIGN: right">106</TD><TD style="TEXT-ALIGN: right">118</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">13/06/2008</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">30</TD><TD style="TEXT-ALIGN: right">24176.0</TD><TD style="TEXT-ALIGN: right">311</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">12/12/2007</TD></TR></TBODY></TABLE>

MVX · Oct 27, 2008

VOG - I think I have sent you wrong html

Excel Workbook

N

O

P

Q

R

S

T

W

1

OFF_ID2

DISPOSE_CO

DISPOSE__1

DISPOSE__2

DISPOSE__3

DISPOSE__4

DISPOSE__5

DATE_CONTR

2

27385.0

282

995

0

12/10/2007

3

20699.0

241

105

106

0

09/11/2007

4

0.0

114

641

313

0

01/12/2007

5

20873.0

663

114

0

12/12/2007

6

26324.0

281

995

0

12/10/2007

7

0.0

118

106

995

0

12/10/2007

8

23695.0

411

106

0

03/10/2007

9

24176.0

103

106

0

12/12/2007

10

21217.0

103

281

284

111

118

0

12/12/2007

11

15718.0

114

115

0

10/01/2008

12

27574.0

103

106

996

0

22/07/2008

13

0.0

103

0

22/07/2008

14

7942.0

114

271

0

22/07/2008

15

15715.0

114

115

0

22/07/2008

16

18567.0

996

118

105

103

0

22/07/2008

17

15715.0

106

118

0

22/07/2008

18

21461.0

114

641

0

23/07/2008

19

19219.0

103

118

0

22/07/2008

20

21099.0

241

103

106

0

22/07/2008

21

18567.0

103

996

0

22/07/2008

22

8029.0

102

0

22/07/2008

23

0.0

106

0

17/07/2008

24

0.0

106

118

0

15/10/2007

25

0.0

241

106

0

17/08/2008

26

0.0

107

0

10/06/2008

27

21462.0

103

499

106

0

11/06/2008

28

27861.0

106

103

0

14/06/2008

na_2008

VoG · Oct 27, 2008

Try running supatest

Rich (BB code):

Sub supatest()
Dim x As Boolean
x = True
Do While x
    Call test(x)
Loop
End Sub
Sub test(z As Boolean)
Dim LR As Long, Found As Range
Set Found = Sheets("Sheet1").Range("O15:T20").Find(what:=999)
If Not Found Is Nothing Then
    With Sheets("Sheet2")
        z = True
        LR = Range("O" & Rows.Count).End(xlUp).Row
        Found.EntireRow.Copy Destination:=.Range("A" & LR + 1)
    End With
    Application.CutCopyMode = False
    Found.EntireRow.Delete
Else
    z = False
End If
End Sub

MVX · Oct 27, 2008

VOG
Many thanks can you advise whether the 'range' is correct?

Set Found = Sheets("Sheet1").Range("O15:T20").Find(what:=999)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>

Will this 'range' check all columns 'O':'T' and all rows 2:43928 in the columns? - perhaps I did not explain properly when I described the 'range' as 'O15' - 'T20' - What i meant was column 'O' (Column Number 15) to column 'T' (Column Number 20) . I want to check all colums and all the rows in the columns. Am I correct and the above checks columns 'O' to 'T' between rows 15 and 20??
Unfortunately the data sheets are differing in the number of rows in each?

Thanking you once again

VoG · Oct 28, 2008

Try

Code:

Sub supatest()
Dim x As Boolean
x = True
Do While x
    Call test(x)
Loop
End Sub
Sub test(z As Boolean)
Dim LR As Long, Found As Range
Set Found = Sheets("Sheet1").Columns("O:T").Find(what:=999)
If Not Found Is Nothing Then
    With Sheets("Sheet2")
        z = True
        LR = Range("O" & Rows.Count).End(xlUp).Row
        Found.EntireRow.Copy Destination:=.Range("A" & LR + 1)
    End With
    Application.CutCopyMode = False
    Found.EntireRow.Delete
Else
    z = False
End If
End Sub

Excel VBA

MVX

New Member

Excel Facts

VoG

Legend

MVX

New Member

MVX

New Member

VoG

Legend

MVX

New Member

MVX

New Member

VoG

Legend

MVX

New Member

VoG

Legend

Similar threads

Forum statistics

Share this page

Excel VBA

New Member

Excel Facts

Legend

New Member

New Member

Legend

New Member

New Member

Legend

New Member

Legend

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock