Macro needed to search for text, then copy & paste the next n rows

trevorr

New Member
Joined
Jan 30, 2014
Messages
5
Hi - I am hoping this will be simple for someone with VBA skills...what i need is a macro that will:
1. search through a column for a specific text string
2. copy all subsequent rows that are not blank
3. paste these values into a new worksheet

E.g. look for "Black" in the column below, then copy and paste the subsequent non-blank rows to a new sheet:

black
a
b
c
black
d
black
e
f
g
h
i
would become:

a
b
c
d
e
f
g
h
i

<tbody>
</tbody>

<tbody>
</tbody>

It would be nice to be asked (via and input box) for which text to search for.

Thanks in advance :)
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Is all you have in the Column the word "black" (or whatever word you are using), blank cells and the data you want copying and nothing else?

and what are the 2 sheet names?
 

trevorr

New Member
Joined
Jan 30, 2014
Messages
5
Is all you have in the Column the word "black" (or whatever word you are using), blank cells and the data you want copying and nothing else?

and what are the 2 sheet names?

Thanks for the quick response, Mark...

Yes, that's all that's in the column.
The sheet names are Sheet1 and Sheet2.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

Try the code below (please note you should have a header row or least the word "black" in A1.

Test on a copy of your data

Rich (BB code):
Sub CopyCol()

    Dim i As String
    i = Application.InputBox(prompt:="What word do you want to use?", Type:=2)

    With Sheets("Sheet1").Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)

        .AutoFilter Field:=1, Criteria1:="<>" & i, _
                    Operator:=xlAnd, Criteria2:="<>"
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)

    End With
    ActiveSheet.AutoFilterMode = False

End Sub
 

trevorr

New Member
Joined
Jan 30, 2014
Messages
5
Thanks, that works for the test data. However, it doesn't work on my real data (I have 1,000,000 rows, so don't want to post it here!)...the issue is that I should have specified that it would need to select only the rows in a range that start immediately after "black" but stops immediately prior to a second specific term, let's call it "white" - see below...

E.g. These data on Sheet1:

black
a
b
c
white
e
f

h

black
i

white
j

l
m
black
n

Would become on Sheet2:
a
b
c
i
n

Essentially then the code would allow for selecting a copying data between two specified terms...sounds simple but is well beyond my modest VBA abilities.

Thanks!

Trevor
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

Not much I can do with the data in that layout as it goes black (items to paste) white(items to paste)black.
If it went black (items to paste) white, black(items to paste) white then I might have been able to do something with it.

What is the data in reality. Are the the white and black text and the rest numbers?

Why can't the data be copied into Sheet2 and then anything you don't want deleted out?
 

trevorr

New Member
Joined
Jan 30, 2014
Messages
5
The data are a single column of entries from a text file that is a failed XML import (sample below). My challenge is to determine for each drug ("# Brand_Names:"), what are the corresponding categories ("# Drug_Category:"). This could be done manually, but it would take very long as there are >1,000,000 data rows in the column.

Here is what I have (repeated for different drugs down a single column for about 1,000,000 rows):

# Absorption:
Bioavailability is 100% following injection.
# Biotransformation:
Lepirudin is thought to be metabolized by release of amino acids via catabolic hydrolysis of the parent drug. However, con-clusive data are not available. About 48% of the administration dose is excreted in the urine which consists of unchanged drug (35%) and other fragments of the parent drug.
# Brand_Mixtures:
Not Available
# Brand_Names:
Refludan
# CAS_Registry_Number:
120993-53-5
# ChEBI_ID:
Not Available
# Chemical_Formula:
C287H440N80O110S6
# Chemical_IUPAC_Name:
Not Available
# Chemical_Structure:
>DB00001 sequence
LVYTDCTESGQNLCLCEGSNVCGQGNKCILGSDGEKNQCVTGEGTPKPQSHNDGDFEEIP
EEYLQ
# Creation_Date:
########
# DPD_Drug_ID_Number:
2240996
# Description:
Lepirudin is identical to natural hirudin except for substitution of leucine for isoleucine at the N-terminal end of the molecule and the absence of a sulfate group on the tyrosine at position 63. It is produced via yeast cells.
# Dosage_Forms:
Powder, for solution
# Drug_Category:
Anticoagulants
Antithrombotic Agents
Fibrinolytic Agents
# Drug_Interactions:
Ginkgo biloba
Treprostinil
# Drug_Reference:
16241940
16244762
16690967
# Drug_Type:
Approved
Biotech

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>


And here is what I need from that data:

Refludan

<tbody>
</tbody>
Anticoagulants

<tbody>
</tbody>
Antithrombotic Agents

<tbody>
</tbody>
Fibrinolytic Agents

<tbody>
</tbody>

<tbody>
</tbody>


While there is only one brand name per drug, there is a variable number of categories (there are 3 in the example above, but there may be 1 or 5 or 3, etc.).
The best solution would be complicated and would also need a 3rd search term, "# Drug_Interactions:", and would look like this:

In Sheet1, start at R1C1 and go down the column (C1) to identify the first row with the value "# Brand_Names:"
Copy the next row (i.e. the row with the actual brand name) and paste it into R1C1 of Sheet2...this the drug name.
Keep going down C1 in Sheet1 until the row value = "# Drug_Category:"
Copy the range that goes from the next row to the row above the row with value = "# Drug_Interactions:" and paste it into R1C2:R1Cx of Sheet2, where x is the number of rows in the range...these are the drug categories.
Now loop to continue down C1 in Sheet1, looking for the next row with the value "# Brand_Names:"...etc until there are no more rows.

Sounds impossible even as I write this, let me know what you think!
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
I was looking to do 2 looping finds based on the original information but looking at your last post this will cause at very least huge hangtime and more likely a crash.
I am afraid at present I can offer you no solution but hopefully one of the MVP's or dictionary scripting experts might see something that I can't at the moment.

What I would say is that if the statement below is anywhere near true and with the type of data you have
different drugs down a single column for about 1,000,000 rows
then I personally feel that Excel might not be the correct tool for this and the word "Database" comes to mind.

I personally would be talking to whoever controls the original data as they will probably have it on a database already and ask if you can have access to it (or a copy of the current database so you can have it in Access), then you can use use the built in tools to manipulate the data or use Excel to pull only the information you actually require from the database.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,575
Members
414,390
Latest member
plimbu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top