PDA

View Full Version : Complicated Macro Help Required!



JBracknell
06-27-2015, 11:36 AM
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
06-27-2015, 07:55 PM
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
06-27-2015, 08:01 PM
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
06-28-2015, 01:39 AM
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
06-28-2015, 01:40 AM
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!