- Nov 17, 2009
Instead of this (from code at the bottom of thread):
I would like to do something like this:
Obviously my attempt does not work, but would appreciate any suggestions. Thanks!
FinalTweeks2 targetRange, "GroupA" FinalTweeks2 targetRange, "GroupB" FinalTweeks2 targetRange, "GroupC"
FinalTweeks2 targetRange, left("Group,5")
Sub FinalTweeks1() Dim targetRange As Range ' change this to where you want to work on Set targetRange = ActiveSheet.Range("A:A") FinalTweeks2 targetRange, "CategoryA" FinalTweeks2 targetRange, "GroupA" FinalTweeks2 targetRange, "GroupB" FinalTweeks2 targetRange, "GroupC" End Sub Sub FinalTweeks2(targetRange As Range, what As String) Dim found As Range, first As Range Set first = targetRange.Find(what, After:=Range("A" & Rows.Count), LookIn:=xlValues, LookAt:=xlWhole) If Not first Is Nothing Then first.Resize(2, 1).EntireRow.Insert 'inserts two rows above "first" Set found = targetRange.FindNext(first) Do While (Not found Is Nothing) If (found.Address = first.Address) Then Exit Do 'found.Clear '''''clears the cell found 'found.EntireRow.Clear 'found.EntireColumn.Clear found.Resize(, 20).Clear '''''changes the size of the range A:E Set found = targetRange.FindNext(found) Loop End If End Sub