Macro to delete rows

[JeffF]

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.

 

[Don Guillett]

Tell us how the rows came to be blue...

 

[Mike H]

Jeff,

Where we we look for "Distr", anywhere on the sheet, in a particular column?
--
Mike

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

 

[Hakyab]

For 1:

mysheet.Range("A1:A13").entirerow.delete

for 3:
dim c as range

set c = mysheet.cells.find "Descr"

do until c is nothing
c.entirerow.delete
set c = nothing
set c = mysheet.cells.findnext
loop


// Find has more parameters which I ignored. Sorry cannot help as easily
with your question 2. Find should be able to get specific formatting, I just
do not know.

Hope this helps,

 

[JeffF]

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

 

[Mike H]

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.

 

[Don Guillett]

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

 

[JeffF]

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.

 

[Mike H]

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.

 

[Jef Gorbach]

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

 

[JeffF]

I'm getting a syntax error here Mike:

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

Thanks,
Jeff

 

[JeffF]

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.

 

[JeffF]

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