Delete Rows within a specified range

[Blessy Clara]

Hi all,

Any help is greatly appreciated -

I need to delete the rows within a specified range. The challenge is that the number of rows varies and inclusive of blank rows)

Range Specifications

Starting Point = Delete Rows that begins with
R&D expertise:
Row 2
Row 3
Row x number
Until

Two rows (which will be blank) above the row that begins with ADD:

Example -

< From
R&D expertise: Development and manufac


turing of in vitro diagnostic controls & calibrators,
purification of human enzymes & proteins,
processing bulk human serum.

Monoclonal antibodies to carbonic anhydrase III
(verifier to myoglobin), development stage;
tumor marker panel control, research.
> Until this blank row (which is the second row, from the row that begins with ADD)
2 AASTON, INC.
Add: 12 Falmouth Rd. Weiiesley, MA 02481

So the range must begin with row containing R&D expertise:
Go until the blank row (which is 2nd row (from bottom up) above the Row containing Add: )

I am using ADD: as end criterion because that is the only data that is consistent -

I hope this is clear

There are 50000 data rows that must be cleaned up - So please render your help.

Thanks and regards
Blessy

 

[Weaver]

try:

Sub test()
    fr = Columns(1).Find(what:="R&D expertise:*").Row
    lr = Columns(1).Find(what:="Add:*").Row - 1
    Rows(fr & ":" & lr).Delete
End Sub

 

[Blessy Clara]

Hi Weaver

Thank you for the code.

Greatly appreciate the response. But when i ran the macro - I happened to notice two things - That
1) it doesn't delete the rows between fr and lr (but deletes other rows before "fr")
2) It deletes only the first instance of occurrence of fr&lr
(I also selected the complete column A before running the code)

Could you please help me with this. Sorry to further bother you.

Thanks and regards
Blessy

 

[Weaver]

On point 2, you didn't mention there would be multiple occurences of the qualifying strings, which might help to explain your perception of the problem with point 1 (the code can't delete lines before the first line it finds, unless 'Add:' occurs before 'R&D Expertise', so if these occur more than once, this could easily be possible)

Selecting the column won't make any difference - the .columns(1) part means it's only looking in column A in any case.

If you could maybe clear up these points, that would be a great help.

W

 

[Weaver]

PS I'm looking at what I have on today - I might not be able to respond until this evening (UK time)

 

[Blessy Clara]

Hi Weaver,

I am very sorry for providing a redundant info-
1) Yes there are multiple occurrences
2) Thank you for explaining the Column selection part (My lack of programming knowledge was the reason for selecting the column)

Given below is the part of the data for clarity
Plz Note - this image is inclusive of blank rows
-------------------------------------------------------------------


1 AALTO SCIENTIFIC, LTD.
Add: 1959 Kellogg Ave. Carlsbad, CA 92008
Tel: (760) 431-7922
Fax: (760) 431-6942
E-mail: kjaalto@msn.com
Web site: www.aaltoscientific.com
President/CEO: Steve Mauro
Research director: Bob Reynolds
R&D expertise: Development and manufac


turing of in vitro diagnostic controls & calibrators,
purification of human enzymes & proteins,
processing bulk human serum.

Technological expertise: E, PA, P/S
Principal products/instrumentation: In vitro

diagnostic controls, including clinical chemistry,
immunoassay, cardiac markers, and specialty
controls.

Main products/instrumentation under R&D:

Monoclonal antibodies to carbonic anhydrase III
(verifier to myoglobin), development stage;
tumor marker panel control, research.

2 AASTON, INC.
Add: 12 Falmouth Rd. Weiiesley, MA 02481
Tel: (781) 237-3755
Fax: (781) 239-0455
E-mail: aaston@aol.com
President: Randolph T. Hatch, Ph.D.

R&D expertise: Research, development,
production and marketing of specialty protein
products.

Technological expertise: RD, F, BA, Q, E,

GA, P/S
Principal products/instrumentation: Specialty
protein: Kappa lock Kappa™ light chain
binding protein, dehydrogenase enzymes, SOD.

3 AASTROM BIOSCIENCES, INC.
Add: 24 Frank Lloyd Wright Dr., P.O. Box 376,
Ann Arbor, MI 48106
Tel: (734) 930-5777
Fax: (734) 665-0485
E-mail: mail@aastrom.com
Web site: www.aastrom.com

President/CEO: R. Douglas Armstrong, Ph.D.
R&D expertise: Cell culture immunology
research, development of systems for stem cell
and blood therapy and gene therapy, DNA
damage analysis.

Technological expertise: TE, CH/S, GT, C/TC
Main products/instrumentation under R&D:

Aastrom Replied™ Cell Production System
for stem cell therapy and other cell therapies;
Aastrom™ Gene Loader for high efficiency
gene transfer; Aastrom cell production system
(stem and progenitor cell expansion from bone
marrow and umbilical cord blood), blood and
immune system recovery in cancer patients

Those Highlighted in Blue is the data that I need to extract and those Highlighted in red is the data that is unnecessary and has to be deleted
So the data/rows commencing from
R&D expertise:"Fr"
To
"Lr" - 2 line before Add: has to be deleted
Such that the names of the next org and the preceding data until the next occurrence of the R&D expertise: is retained. There are 30, 000 organizations data as this (which would be very tedious to be done manually). Any help would be very useful.

I hope I am more clearer this time. Thank you.

 

[Weaver]

try this:

Sub test()
    Dim f As Range, l As Range
    Dim r As Long
    Application.ScreenUpdating = False
    Set f = Columns(1).Find(what:="R&D expertise:*", after:=Range("A1"))
    r = f.Row
    Do While Not f Is Nothing
        Set l = Columns(1).Find(what:="Add:*", after:=f).Offset(-1)
        If l.Row < r Then
            Set l = Cells(Rows.Count, 1).End(xlUp).Offset(1)
        End If
        Range(f, l.Offset(-1)).EntireRow.Delete
        Set f = Columns(1).Find(what:="R&D expertise:*", after:=l)
    Loop
    On Error Resume Next
    Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

 

[Blessy Clara]

Hi Weaver,

Wow!! thanks alot, that was very great and the output was exactly what i required. Thank you for your effort

 

[Weaver]

Hi Weaver,

Wow!! thanks alot, that was very great and the output was exactly what i required. Thank you for your effort

No problem

 

[cellecell]

Nice but much more formula there is in excel just like plz have a look

One of Excel's most useful features is that it allows users to create custom formulas to perform calculations on their data. Excel also contains built-in formulas called functions that make it easy to perform common calculations on data. Here you will find step by step tutorials, tips and shortcuts on how to use the common and less common functions available in Excel.and in advance excel any other interesting formula is there...
Miracles of Stem Cells