I have a spreadshhet that looks up stocks and the information that I want for
that stock. I am trying to create a grouping. Each stock has a category and
when it is imported it is labeled with that category. What I am looking to do
is if there is a way to delate the category name after the forst occurence
looked up. So in the example below after the first US OE Speacilty-RealEstate
have excel delete just the next occurence heading but leave the other
information that follows. So it is like US OE Speacilty Real Estate is a
group of funds.

US OE Specialty-Real Estate
SUSIX JPMORGAN U.S. REAL ESTATE A
DJ WILSHIRE REIT TR
MORNINGSTAR RANK

US OE Specialty-Real Estate
MUSDX MORGAN STANLEY INST US REAL ESTATE P
DJ Wilshire REIT TR
MORNINGSTAR RANK

US OE Foreign Large Value
CIVVX CAUSEWAY INTERNATIONAL VALUE INV
MSCI EAFE NR USD
MORNINGSTAR RANK

US OE Mid-Cap Value
MLUSX MASSMUTUAL SELECT MID-CAP VALUE S
RUSSELL MID CAP VALUE TR
MORNINGSTAR RANK

try this.

Sub deletecattitles()
mc = "a"
For i = Cells(Rows.Count, mc).End(xlUp).Row - 3 To 1 Step -5
'MsgBox Cells(i, mc)
If Cells(i + 5, mc) = Cells(i, mc) Then
Rows(i + 8).Delete
Rows(i + 7).Delete
Rows(i + 4).Resize(2).Delete
End If
Next i
End Sub

OR to JUST delete the second title and blank row
Sub deletecattitles()
[cpyblk].Copy Range("a1")
mc = "a"
For i = Cells(Rows.Count, mc).End(xlUp).Row - 3 To 1 Step -5
'MsgBox Cells(i, mc)
If Cells(i + 5, mc) = Cells(i, mc) Then
'Rows(i + 8).Delete
'Rows(i + 7).Delete
Rows(i + 4).Resize(2).Delete
End If
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"daphoenix" <(E-Mail Removed)> wrote in message
news:88F7F7B0-5822-460C-B4D4-(E-Mail Removed)...
>I have a spreadshhet that looks up stocks and the information that I want
>for
> that stock. I am trying to create a grouping. Each stock has a category
> and
> when it is imported it is labeled with that category. What I am looking to
> do
> is if there is a way to delate the category name after the forst occurence
> looked up. So in the example below after the first US OE
> Speacilty-RealEstate
> have excel delete just the next occurence heading but leave the other
> information that follows. So it is like US OE Speacilty Real Estate is a
> group of funds.
>
> US OE Specialty-Real Estate
> SUSIX JPMORGAN U.S. REAL ESTATE A
> DJ WILSHIRE REIT TR
> MORNINGSTAR RANK
>
> US OE Specialty-Real Estate
> MUSDX MORGAN STANLEY INST US REAL ESTATE P
> DJ Wilshire REIT TR
> MORNINGSTAR RANK
>
> US OE Foreign Large Value
> CIVVX CAUSEWAY INTERNATIONAL VALUE INV
> MSCI EAFE NR USD
> MORNINGSTAR RANK
>
> US OE Mid-Cap Value
> MLUSX MASSMUTUAL SELECT MID-CAP VALUE S
> RUSSELL MID CAP VALUE TR
> MORNINGSTAR RANK
>

thanks for such the timly reply..
i tried the first code, and it deletes everything but the first
line(realestate) and the second line.

this is what i start with:

US OE Specialty-Real Estate
SUSIX JPMORGAN U.S. REAL ESTATE A
DJ WILSHIRE REIT TR
MORNINGSTAR RANK

US OE Specialty-Real Estate
MUSDX MORGAN STANLEY INST US REAL ESTATE P
DJ Wilshire REIT TR
MORNINGSTAR RANK

and the result I am looking to get is something like this:

US OE Specialty-Real Estate
SUSIX JPMORGAN U.S. REAL ESTATE A
DJ WILSHIRE REIT TR
MORNINGSTAR RANK

MUSDX MORGAN STANLEY INST US REAL ESTATE P
DJ Wilshire REIT TR
MORNINGSTAR RANK

and if there is a way to keep the formatting of the cells i.e. fill color
that would be a dded bonus....

thanks again for the help



"Don Guillett" wrote:

> try this.
>
> Sub deletecattitles()
> mc = "a"
> For i = Cells(Rows.Count, mc).End(xlUp).Row - 3 To 1 Step -5
> 'MsgBox Cells(i, mc)
> If Cells(i + 5, mc) = Cells(i, mc) Then
> Rows(i + 8).Delete
> Rows(i + 7).Delete
> Rows(i + 4).Resize(2).Delete
> End If
> Next i
> End Sub
>
> OR to JUST delete the second title and blank row
> Sub deletecattitles()
> [cpyblk].Copy Range("a1")
> mc = "a"
> For i = Cells(Rows.Count, mc).End(xlUp).Row - 3 To 1 Step -5
> 'MsgBox Cells(i, mc)
> If Cells(i + 5, mc) = Cells(i, mc) Then
> 'Rows(i + 8).Delete
> 'Rows(i + 7).Delete
> Rows(i + 4).Resize(2).Delete
> End If
> Next i
> End Sub
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "daphoenix" <(E-Mail Removed)> wrote in message
> news:88F7F7B0-5822-460C-B4D4-(E-Mail Removed)...
> >I have a spreadshhet that looks up stocks and the information that I want
> >for
> > that stock. I am trying to create a grouping. Each stock has a category
> > and
> > when it is imported it is labeled with that category. What I am looking to
> > do
> > is if there is a way to delate the category name after the forst occurence
> > looked up. So in the example below after the first US OE
> > Speacilty-RealEstate
> > have excel delete just the next occurence heading but leave the other
> > information that follows. So it is like US OE Speacilty Real Estate is a
> > group of funds.
> >
> > US OE Specialty-Real Estate
> > SUSIX JPMORGAN U.S. REAL ESTATE A
> > DJ WILSHIRE REIT TR
> > MORNINGSTAR RANK
> >
> > US OE Specialty-Real Estate
> > MUSDX MORGAN STANLEY INST US REAL ESTATE P
> > DJ Wilshire REIT TR
> > MORNINGSTAR RANK
> >
> > US OE Foreign Large Value
> > CIVVX CAUSEWAY INTERNATIONAL VALUE INV
> > MSCI EAFE NR USD
> > MORNINGSTAR RANK
> >
> > US OE Mid-Cap Value
> > MLUSX MASSMUTUAL SELECT MID-CAP VALUE S
> > RUSSELL MID CAP VALUE TR
> > MORNINGSTAR RANK
> >
>
>

Give this subroutine a try...

Sub DeleteMultiples(ItemText As String)
Dim X As Long
Dim ItemRow As Long
Dim LastRow As Long
Const ColumnToProcess = "A"
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, ColumnToProcess).End(xlUp).Row
ItemRow = .Range(ColumnToProcess & ":" & ColumnToProcess). _
Find(ItemText, After:=Cells(LastRow, "A"), _
LookIn:=xlValues).Row
For X = LastRow To ItemRow + 1 Step -1
If Cells(X, ColumnToProcess).Value = ItemText Then
Cells(X, ColumnToProcess).EntireRow.Delete
End If
Next
End With
End Sub

You would call it from your own macro like this...

Sub MyMacro()
DeleteMultiples "US OE Specialty-Real Estate"
End Sub

Rick


"daphoenix" <(E-Mail Removed)> wrote in message
news:88F7F7B0-5822-460C-B4D4-(E-Mail Removed)...
>I have a spreadshhet that looks up stocks and the information that I want
>for
> that stock. I am trying to create a grouping. Each stock has a category
> and
> when it is imported it is labeled with that category. What I am looking to
> do
> is if there is a way to delate the category name after the forst occurence
> looked up. So in the example below after the first US OE
> Speacilty-RealEstate
> have excel delete just the next occurence heading but leave the other
> information that follows. So it is like US OE Speacilty Real Estate is a
> group of funds.
>
> US OE Specialty-Real Estate
> SUSIX JPMORGAN U.S. REAL ESTATE A
> DJ WILSHIRE REIT TR
> MORNINGSTAR RANK
>
> US OE Specialty-Real Estate
> MUSDX MORGAN STANLEY INST US REAL ESTATE P
> DJ Wilshire REIT TR
> MORNINGSTAR RANK
>
> US OE Foreign Large Value
> CIVVX CAUSEWAY INTERNATIONAL VALUE INV
> MSCI EAFE NR USD
> MORNINGSTAR RANK
>
> US OE Mid-Cap Value
> MLUSX MASSMUTUAL SELECT MID-CAP VALUE S
> RUSSELL MID CAP VALUE TR
> MORNINGSTAR RANK
>

You SHOULD have been able to THINK this out yourself. Don't know about the
formatting I can't see. BTW, I was a series 7 license holder when I was a
Regional Manager for ING.

Sub deletecattitles()
mc = "a"
For i = Cells(Rows.Count, mc).End(xlUp).Row - 3 To 1 Step -5
'MsgBox Cells(i, mc)
If Cells(i + 5, mc) = Cells(i, mc) Then
Rows(i + 5).Delete
End If
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"daphoenix" <(E-Mail Removed)> wrote in message
news:3563EFEA-5371-4E0E-87C8-(E-Mail Removed)...
> thanks for such the timly reply..
> i tried the first code, and it deletes everything but the first
> line(realestate) and the second line.
>
> this is what i start with:
>
> US OE Specialty-Real Estate
> SUSIX JPMORGAN U.S. REAL ESTATE A
> DJ WILSHIRE REIT TR
> MORNINGSTAR RANK
>
> US OE Specialty-Real Estate
> MUSDX MORGAN STANLEY INST US REAL ESTATE P
> DJ Wilshire REIT TR
> MORNINGSTAR RANK
>
> and the result I am looking to get is something like this:
>
> US OE Specialty-Real Estate
> SUSIX JPMORGAN U.S. REAL ESTATE A
> DJ WILSHIRE REIT TR
> MORNINGSTAR RANK
>
> MUSDX MORGAN STANLEY INST US REAL ESTATE P
> DJ Wilshire REIT TR
> MORNINGSTAR RANK
>
> and if there is a way to keep the formatting of the cells i.e. fill color
> that would be a dded bonus....
>
> thanks again for the help
>
>
>
> "Don Guillett" wrote:
>
>> try this.
>>
>> Sub deletecattitles()
>> mc = "a"
>> For i = Cells(Rows.Count, mc).End(xlUp).Row - 3 To 1 Step -5
>> 'MsgBox Cells(i, mc)
>> If Cells(i + 5, mc) = Cells(i, mc) Then
>> Rows(i + 8).Delete
>> Rows(i + 7).Delete
>> Rows(i + 4).Resize(2).Delete
>> End If
>> Next i
>> End Sub
>>
>> OR to JUST delete the second title and blank row
>> Sub deletecattitles()
>> [cpyblk].Copy Range("a1")
>> mc = "a"
>> For i = Cells(Rows.Count, mc).End(xlUp).Row - 3 To 1 Step -5
>> 'MsgBox Cells(i, mc)
>> If Cells(i + 5, mc) = Cells(i, mc) Then
>> 'Rows(i + 8).Delete
>> 'Rows(i + 7).Delete
>> Rows(i + 4).Resize(2).Delete
>> End If
>> Next i
>> End Sub
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "daphoenix" <(E-Mail Removed)> wrote in message
>> news:88F7F7B0-5822-460C-B4D4-(E-Mail Removed)...
>> >I have a spreadshhet that looks up stocks and the information that I
>> >want
>> >for
>> > that stock. I am trying to create a grouping. Each stock has a category
>> > and
>> > when it is imported it is labeled with that category. What I am looking
>> > to
>> > do
>> > is if there is a way to delate the category name after the forst
>> > occurence
>> > looked up. So in the example below after the first US OE
>> > Speacilty-RealEstate
>> > have excel delete just the next occurence heading but leave the other
>> > information that follows. So it is like US OE Speacilty Real Estate is
>> > a
>> > group of funds.
>> >
>> > US OE Specialty-Real Estate
>> > SUSIX JPMORGAN U.S. REAL ESTATE A
>> > DJ WILSHIRE REIT TR
>> > MORNINGSTAR RANK
>> >
>> > US OE Specialty-Real Estate
>> > MUSDX MORGAN STANLEY INST US REAL ESTATE P
>> > DJ Wilshire REIT TR
>> > MORNINGSTAR RANK
>> >
>> > US OE Foreign Large Value
>> > CIVVX CAUSEWAY INTERNATIONAL VALUE INV
>> > MSCI EAFE NR USD
>> > MORNINGSTAR RANK
>> >
>> > US OE Mid-Cap Value
>> > MLUSX MASSMUTUAL SELECT MID-CAP VALUE S
>> > RUSSELL MID CAP VALUE TR
>> > MORNINGSTAR RANK
>> >
>>
>>

i cant seem to get this routine to work when calling MyMacro

"daphoenix" wrote:

> I have a spreadshhet that looks up stocks and the information that I want for
> that stock. I am trying to create a grouping. Each stock has a category and
> when it is imported it is labeled with that category. What I am looking to do
> is if there is a way to delate the category name after the forst occurence
> looked up. So in the example below after the first US OE Speacilty-RealEstate
> have excel delete just the next occurence heading but leave the other
> information that follows. So it is like US OE Speacilty Real Estate is a
> group of funds.
>
> US OE Specialty-Real Estate
> SUSIX JPMORGAN U.S. REAL ESTATE A
> DJ WILSHIRE REIT TR
> MORNINGSTAR RANK
>
> US OE Specialty-Real Estate
> MUSDX MORGAN STANLEY INST US REAL ESTATE P
> DJ Wilshire REIT TR
> MORNINGSTAR RANK
>
> US OE Foreign Large Value
> CIVVX CAUSEWAY INTERNATIONAL VALUE INV
> MSCI EAFE NR USD
> MORNINGSTAR RANK
>
> US OE Mid-Cap Value
> MLUSX MASSMUTUAL SELECT MID-CAP VALUE S
> RUSSELL MID CAP VALUE TR
> MORNINGSTAR RANK
>

ok i get an error saying object variable or with block variable not set

what does this exactly mean

"daphoenix" wrote:

> I have a spreadshhet that looks up stocks and the information that I want for
> that stock. I am trying to create a grouping. Each stock has a category and
> when it is imported it is labeled with that category. What I am looking to do
> is if there is a way to delate the category name after the forst occurence
> looked up. So in the example below after the first US OE Speacilty-RealEstate
> have excel delete just the next occurence heading but leave the other
> information that follows. So it is like US OE Speacilty Real Estate is a
> group of funds.
>
> US OE Specialty-Real Estate
> SUSIX JPMORGAN U.S. REAL ESTATE A
> DJ WILSHIRE REIT TR
> MORNINGSTAR RANK
>
> US OE Specialty-Real Estate
> MUSDX MORGAN STANLEY INST US REAL ESTATE P
> DJ Wilshire REIT TR
> MORNINGSTAR RANK
>
> US OE Foreign Large Value
> CIVVX CAUSEWAY INTERNATIONAL VALUE INV
> MSCI EAFE NR USD
> MORNINGSTAR RANK
>
> US OE Mid-Cap Value
> MLUSX MASSMUTUAL SELECT MID-CAP VALUE S
> RUSSELL MID CAP VALUE TR
> MORNINGSTAR RANK
>

Who did you write this message to... Don or me? Either way, it would be a
good idea to post your MyMacro code so we can see what you tried to do.
Also, it would help if you described what didn't work... Did you get an
error message? Did nothing happen? Did something happen that you didn't
expect?

Rick


"daphoenix" <(E-Mail Removed)> wrote in message
news:A0F91701-3327-42C9-9E83-(E-Mail Removed)...
>i cant seem to get this routine to work when calling MyMacro
>
> "daphoenix" wrote:
>
>> I have a spreadshhet that looks up stocks and the information that I want
>> for
>> that stock. I am trying to create a grouping. Each stock has a category
>> and
>> when it is imported it is labeled with that category. What I am looking
>> to do
>> is if there is a way to delate the category name after the forst
>> occurence
>> looked up. So in the example below after the first US OE
>> Speacilty-RealEstate
>> have excel delete just the next occurence heading but leave the other
>> information that follows. So it is like US OE Speacilty Real Estate is a
>> group of funds.
>>
>> US OE Specialty-Real Estate
>> SUSIX JPMORGAN U.S. REAL ESTATE A
>> DJ WILSHIRE REIT TR
>> MORNINGSTAR RANK
>>
>> US OE Specialty-Real Estate
>> MUSDX MORGAN STANLEY INST US REAL ESTATE P
>> DJ Wilshire REIT TR
>> MORNINGSTAR RANK
>>
>> US OE Foreign Large Value
>> CIVVX CAUSEWAY INTERNATIONAL VALUE INV
>> MSCI EAFE NR USD
>> MORNINGSTAR RANK
>>
>> US OE Mid-Cap Value
>> MLUSX MASSMUTUAL SELECT MID-CAP VALUE S
>> RUSSELL MID CAP VALUE TR
>> MORNINGSTAR RANK
>>

rick, thanks for the help

i am getting runtime error 91 with this for the item row section :

Sub MyMacro()
DeleteMultiples "US OE Specialty-Real Estate"
End Sub

Sub DeleteMultiples(ItemText As String)
Dim X As Long
Dim ItemRow As Long
Dim LastRow As Long
Const ColumnToProcess = "B"
With Worksheets("Psummary")
LastRow = .Cells(Rows.Count, ColumnToProcess).End(xlUp).Row
ItemRow = .Range(ColumnToProcess & ":" & ColumnToProcess). _
Find(ItemText, After:=Cells(LastRow, "B"), _
LookIn:=xlValues).Row
For X = LastRow To ItemRow + 1 Step -1
If Cells(X, ColumnToProcess).Value = ItemText Then
Cells(X, ColumnToProcess).EntireRow.Delete
End If
Next
End With
End Sub


"daphoenix" wrote:

> ok i get an error saying object variable or with block variable not set
>
> what does this exactly mean
>
> "daphoenix" wrote:
>
> > I have a spreadshhet that looks up stocks and the information that I want for
> > that stock. I am trying to create a grouping. Each stock has a category and
> > when it is imported it is labeled with that category. What I am looking to do
> > is if there is a way to delate the category name after the forst occurence
> > looked up. So in the example below after the first US OE Speacilty-RealEstate
> > have excel delete just the next occurence heading but leave the other
> > information that follows. So it is like US OE Speacilty Real Estate is a
> > group of funds.
> >
> > US OE Specialty-Real Estate
> > SUSIX JPMORGAN U.S. REAL ESTATE A
> > DJ WILSHIRE REIT TR
> > MORNINGSTAR RANK
> >
> > US OE Specialty-Real Estate
> > MUSDX MORGAN STANLEY INST US REAL ESTATE P
> > DJ Wilshire REIT TR
> > MORNINGSTAR RANK
> >
> > US OE Foreign Large Value
> > CIVVX CAUSEWAY INTERNATIONAL VALUE INV
> > MSCI EAFE NR USD
> > MORNINGSTAR RANK
> >
> > US OE Mid-Cap Value
> > MLUSX MASSMUTUAL SELECT MID-CAP VALUE S
> > RUSSELL MID CAP VALUE TR
> > MORNINGSTAR RANK
> >

It would be better if you would post your response in the sub-thread you are
responding to rather than start a new sub-thread every time you post a
response.

Okay, I just looked through my code and noticed I left out some "dots" that
are necessary to force the references back to the worksheet referenced in
the With statement. I also see that I left in a hard-coded column reference
instead of using the ColumnToProcess name defined in the Const statement
(you caught that when you substituted your "B" for my hard-coded "A" in the
Cells function call). Below is revised code to correct those errors. See if
it solves your problem.

Sub DeleteMultiples(ItemText As String)
Dim X As Long
Dim ItemRow As Long
Dim LastRow As Long
Const ColumnToProcess = "B"
With Worksheets("Psummary")
LastRow = .Cells(Rows.Count, ColumnToProcess).End(xlUp).Row
ItemRow = .Range(ColumnToProcess & ":" & ColumnToProcess). _
Find(ItemText, After:=.Cells(LastRow, ColumnToProcess), _
LookIn:=xlValues).Row
For X = LastRow To ItemRow + 1 Step -1
If .Cells(X, ColumnToProcess).Value = ItemText Then
.Cells(X, ColumnToProcess).EntireRow.Delete
End If
Next
End With
End Sub

Rick


"daphoenix" <(E-Mail Removed)> wrote in message
news:0AC53C1C-452B-4ACB-9C70-(E-Mail Removed)...
> rick, thanks for the help
>
> i am getting runtime error 91 with this for the item row section :
>
> Sub MyMacro()
> DeleteMultiples "US OE Specialty-Real Estate"
> End Sub
>
> Sub DeleteMultiples(ItemText As String)
> Dim X As Long
> Dim ItemRow As Long
> Dim LastRow As Long
> Const ColumnToProcess = "B"
> With Worksheets("Psummary")
> LastRow = .Cells(Rows.Count, ColumnToProcess).End(xlUp).Row
> ItemRow = .Range(ColumnToProcess & ":" & ColumnToProcess). _
> Find(ItemText, After:=Cells(LastRow, "B"), _
> LookIn:=xlValues).Row
> For X = LastRow To ItemRow + 1 Step -1
> If Cells(X, ColumnToProcess).Value = ItemText Then
> Cells(X, ColumnToProcess).EntireRow.Delete
> End If
> Next
> End With
> End Sub
>
>
> "daphoenix" wrote:
>
>> ok i get an error saying object variable or with block variable not set
>>
>> what does this exactly mean
>>
>> "daphoenix" wrote:
>>
>> > I have a spreadshhet that looks up stocks and the information that I
>> > want for
>> > that stock. I am trying to create a grouping. Each stock has a category
>> > and
>> > when it is imported it is labeled with that category. What I am looking
>> > to do
>> > is if there is a way to delate the category name after the forst
>> > occurence
>> > looked up. So in the example below after the first US OE
>> > Speacilty-RealEstate
>> > have excel delete just the next occurence heading but leave the other
>> > information that follows. So it is like US OE Speacilty Real Estate is
>> > a
>> > group of funds.
>> >
>> > US OE Specialty-Real Estate
>> > SUSIX JPMORGAN U.S. REAL ESTATE A
>> > DJ WILSHIRE REIT TR
>> > MORNINGSTAR RANK
>> >
>> > US OE Specialty-Real Estate
>> > MUSDX MORGAN STANLEY INST US REAL ESTATE P
>> > DJ Wilshire REIT TR
>> > MORNINGSTAR RANK
>> >
>> > US OE Foreign Large Value
>> > CIVVX CAUSEWAY INTERNATIONAL VALUE INV
>> > MSCI EAFE NR USD
>> > MORNINGSTAR RANK
>> >
>> > US OE Mid-Cap Value
>> > MLUSX MASSMUTUAL SELECT MID-CAP VALUE S
>> > RUSSELL MID CAP VALUE TR
>> > MORNINGSTAR RANK
>> >