Complicated Macro Help Required!

[JBracknell]

Hi all,

Thanks for taking the time to read this. Here is the template from which I'll explain my struggle. I've been struggling on this for two weeks so I hope you can help me!

EXAMPLE
Cat	Dog	Pig	Sheep
Q	F	T	1
R	E	Y	2
Z	S	H	3
Cat	Dog	Pig	Sheep
A	H	K	1
B	I	L	2
Cat	Dog	Pig	Sheep
V	E	T	1
C	S	I	2
B	N	K	3
Y	S	I	2
T	N	K	3

Firstly, I need a macro code that deletes the second "CAT. DOG. PIG. SHEEP." row only. The monthly report means that this row varies on where it could be, although the columns will never change. Furthermore, I also need the two rows above that deleted. There will always be two rows above it that are unnecessary.

After a successful run of this macro, the table should look like this:

EXAMPLE
Cat	Dog	Pig	Sheep
Q	F	T	1
R	E	Y	2
Z	S	H	3
A	H	K	1
B	I	L	2
Cat	Dog	Pig	Sheep
V	E	T	1
C	S	I	2
B	N	K	3
Y	S	I	2
T	N	K	3

Secondly, I need the cell "Sheep" and all the data underneath it in the first section only deleted. Those cells must then have "No Border" and "No Fill". While the example provided has five cells underneath "Sheep", this is wholly variable. The "Cat. Dog. Pig. Sheep" headline is also unalterable, just to complicate things some more! In the second section, the cell "Sheep" and the data underneath it must remain untouched.

EXAMPLE
Cat	Dog	Pig
Q	F	T
R	E	Y
Z	S	H
A	H	K
B	I	L
Cat	Dog	Pig	Sheep
V	E	T	1
C	S	I	2
B	N	K	3
Y	S	I	2
T	N	K	3

Thanks a lot guys. Been struggling on this for two weeks!

Jake,

[SamT]

The number of empty rows in your table are not clear, so you may have to mess around with the - offset numbers.

Sub SamT()
Range("A1").Find(What:=Range("A1").Value, After:=Range("A1"), SearchDirection:=xlNext) _
      .Offset(-2).Resize(3, 4).Delete
Range(Range("E1"), Range("E1").Find(What:=Range("E1").Value, After:=Range("E1"), _
      SearchDirection:=xlNext).Offset(-3)).Delete shift:=xlShiftToLeft
End Sub

[mikerickson]

If there is a blank row between Z and Cat, you could use this

With Range("A:D").SpecialCells(xlCellTypeConstants)
    With .Areas(2)
        .Rows(1).EntireRow.Delete
    End With
    Range(.Areas(1), .Areas(2)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With

[JBracknell]

The number of empty rows in your table are not clear, so you may have to mess around with the - offset numbers.

Sub SamT()
Range("A1").Find(What:=Range("A1").Value, After:=Range("A1"), SearchDirection:=xlNext) _
      .Offset(-2).Resize(3, 4).Delete
Range(Range("E1"), Range("E1").Find(What:=Range("E1").Value, After:=Range("E1"), _
      SearchDirection:=xlNext).Offset(-3)).Delete shift:=xlShiftToLeft
End Sub

Thanks so much!

[JBracknell]

If there is a blank row between Z and Cat, you could use this

With Range("A:D").SpecialCells(xlCellTypeConstants)
    With .Areas(2)
        .Rows(1).EntireRow.Delete
    End With
    Range(.Areas(1), .Areas(2)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With

Thank you so much!