deleting condition after the first duplicate
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" wrote in message
...
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
|