Prev: Set certain cells to print and others not to print
Next: Filling right header/footer with barcode
From: Mike H on 17 Mar 2010 14:56
Hi,

Your clarification has; to me at least, made things less clear. Are we
deleting rows where we find 'District' or Facility Name" and don't repeat"?

Do we automatically delete the first 13 rows as indicated in you first post
or begin a search for an ambiguous search string on row 5?

Lastly 'Blue' is a pretty non descript term, in the Excel pallet there are
many colours that could be described as 'Blue'. My macro uses a Blue which is
colorindex 5


Sub delete_Me2()
Dim CopyRange As Range
Dim LastRow as Long
Set sht = Sheets("Sheet1")' Chane to suit
Set CopyRange = sht.Rows("1:13")
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A14:A" & lastrow)
For Each c In MyRange
If c.Interior.ColorIndex = 5 Or UCase(c.Value) = "DISTRICT" Then
Set CopyRange = Union(CopyRange, c.EntireRow)
End If

Next
CopyRange.Delete
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"JeffF" wrote:

> Thanks for the replies all.
>
> For #2, its actually not "Distr", it is "District" and it is only in column
> A. So I would want it to go: search in column A for the word "District"; when
> found, delete the row it is in; repeat until no more found.
>
> How they became blue?... this is an export from a web database. The export
> adds all districts to one worksheet. They put that blue header row and a few
> blank rows at the beginning of each district. We don't care about separating
> them by districts so we just want all of the data to be contiguous. And, we
> can't properly filter if those header rows are in the way.
>
> If you could say "start the macro on row 5 and delete any row where you find
> the phrase "Facility Name" and don't repeat" that would do it as well, as
> "Facility Name" is one of the headers that I doubt would ever appear as real
> text in the worksheet.
>
> Thanks again,
> Jeff
>
From: Don Guillett on 17 Mar 2010 15:00
sub deletem() 'change =6 to your colorindex number
dim i as long
rows("1:13").delete
for i=cells(rows.count,1).end(xlup).row to 2 step-1
if cells(i,1)="District" or cells(i,1).interior.colorindex=6 then
rows(i).delete
next i
end sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"JeffF" <JeffF(a)discussions.microsoft.com> wrote in message
news:7DB7908B-CC56-4C00-9C6E-29AB69F35493(a)microsoft.com...
> Thanks for the replies all.
>
> For #2, its actually not "Distr", it is "District" and it is only in
> column
> A. So I would want it to go: search in column A for the word "District";
> when
> found, delete the row it is in; repeat until no more found.
>
> How they became blue?... this is an export from a web database. The export
> adds all districts to one worksheet. They put that blue header row and a
> few
> blank rows at the beginning of each district. We don't care about
> separating
> them by districts so we just want all of the data to be contiguous. And,
> we
> can't properly filter if those header rows are in the way.
>
> If you could say "start the macro on row 5 and delete any row where you
> find
> the phrase "Facility Name" and don't repeat" that would do it as well, as
> "Facility Name" is one of the headers that I doubt would ever appear as
> real
> text in the worksheet.
>
> Thanks again,
> Jeff
>

From: JeffF on 17 Mar 2010 15:06
To clarify:
1) Delete rows 1-13
2) Delete all rows where the word "District" appears in column A
3) Find all rows AFTER row 1 that are highlighted in blue and delete them
(these are separators that would mess up my sorting and I don't need them.
They come in with these cells filled with the color R 204, G 255, B 204).

For #3, I can't say "delete any row that has a specific word" to delete the
header because the header is actually 3 different rows, only one of which
would have the specific word.

Thanks again.

"JeffF" wrote:

> I'd appreciate some help in creating a macro to delete rows.
> Here's what I need:
> 1) Delete rows 1-13
> 2) Then find all rows that are highlited blue and delete them
> 3) Then find all rows that include the text "Distr" and delete them
>
> Suggestions?
> Thanks in advance.
>
From: Mike H on 17 Mar 2010 15:36
Jeff,

Based upon your latest description, my modified macro. Note I've used the
RGB numbers you provided but it looks suspiciously green to me.

Sub delete_Me2()
Dim CopyRange As Range
Set sht = Sheets("Sheet1")
sht.Rows("1:13").Delete
lastrow = sht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = sht.Range("A1:A" & lastrow)
For Each c In MyRange
If c.Interior.Color = RGB(204, 255, 204) Or UCase(c.Value) = "DISTRICT"
Then
If CopyRange Is Nothing Then
Set CopyRange = c.EntireRow
Else
Set CopyRange = Union(CopyRange, c.EntireRow)
End If
End If
Next
If Not CopyRange Is Nothing Then
CopyRange.Delete
End If
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"JeffF" wrote:

> To clarify:
> 1) Delete rows 1-13
> 2) Delete all rows where the word "District" appears in column A
> 3) Find all rows AFTER row 1 that are highlighted in blue and delete them
> (these are separators that would mess up my sorting and I don't need them.
> They come in with these cells filled with the color R 204, G 255, B 204).
>
> For #3, I can't say "delete any row that has a specific word" to delete the
> header because the header is actually 3 different rows, only one of which
> would have the specific word.
>
> Thanks again.
>
> "JeffF" wrote:
>
> > I'd appreciate some help in creating a macro to delete rows.
> > Here's what I need:
> > 1) Delete rows 1-13
> > 2) Then find all rows that are highlited blue and delete them
> > 3) Then find all rows that include the text "Distr" and delete them
> >
> > Suggestions?
> > Thanks in advance.
> >
From: Jef Gorbach on 17 Mar 2010 16:59
On Mar 17, 3:06 pm, JeffF <Je...(a)discussions.microsoft.com> wrote:
> To clarify:
> 1) Delete rows 1-13
> 2) Delete all rows where the word "District" appears in column A
> 3) Find all rows AFTER row 1 that are highlighted in blue and delete them
> (these are separators that would mess up my sorting and I don't need them..
> They come in with these cells filled with the color R 204, G 255, B 204).

First, let's find out what color Excel is using for the interior color
fill for the rows the web application is rendering "blue"
Sub findcolor()
MsgBox (Range("A4").Interior.ColorIndex)
End Sub

then give this a try:
Sub test()
Dim FilterRange As Range
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

'for #1 - delete rows 1 thru 13
Range("A1:A13").EntireRow.Delete

'for #2 - delete rows where column(a)="district"
'change H to whatever your last column is to include the entire area.
Set FilterRange = Range("A1:H" & FinalRow)
FilterRange.AutoFilter Field:=1, Criteria1:="District"
FilterRange.SpecialCells(xlCellTypeVisible).EntireRow.Delete

'for #3 - delete blue rows
For Each c In Range("A2:A" & FinalRow)
'presuming FindColor returned -4142
If c.Interior.ColorIndex = -4142 Then c.EntireRow.Delete
Next
End Sub


sub test()
range("A1:A13").entirerow.delete
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Set certain cells to print and others not to print
Next: Filling right header/footer with barcode