deleting condition after the first duplicate

[daphoenix]

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

 

[Don Guillett]

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

 

[daphoenix]

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

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 address 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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Don Guillett]

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 address 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

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 address 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

 

[daphoenix]

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

 

[daphoenix]

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

what does this exactly mean

 

[Rick Rothstein \(MVP - VB\)]

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]

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

 

[Rick Rothstein \(MVP - VB\)]

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