Prev: Set certain cells to print and others not to print
Next: Filling right header/footer with barcode
From: JeffF on 18 Mar 2010 10:04
I'm getting a syntax error here Mike:

If c.Interior.Color = RGB(204, 255, 204) Or UCase(c.Value) = "DISTRICT"

Thanks,
Jeff

"Mike H" wrote:

> 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: JeffF on 18 Mar 2010 10:26
This one is close Jef.
The green blue color returned "35". I added that to the routine and it
worked; however, it is deleting Row 1, which is the only green/blue header I
want to keep.

Also, deleting "District:" is not working.

"Jef Gorbach" wrote:

> 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
> .
>
From: JeffF on 18 Mar 2010 10:44
Thank you everybody who helped. Here is what I ended up with. It seems to
work great.

Sub DeleteJunk()
Dim FilterRange As Range
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

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

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

'delete blue rows
For Each c In Range("A3:AC" & FinalRow)
If c.Interior.ColorIndex = 35 Then c.EntireRow.Delete
Next

'delete all rows that contain no data
Dim i As Long
Dim lLastRow As Long
lLastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
Application.ScreenUpdating = False
For i = lLastRow To 1 Step -1
If WorksheetFunction.CountA(ActiveSheet.Rows(i)) = 0 Then
ActiveSheet.Rows(i).EntireRow.Delete
End If
Next i
Application.ScreenUpdating = True
End Sub



"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.
>
First  |  Prev  | 
Pages: 1 2 3
Prev: Set certain cells to print and others not to print
Next: Filling right header/footer with barcode