kylefoley76
Well-known Member
- Joined
- Mar 1, 2010
- Messages
- 1,553
Code:
Columns("D:D").Select
Selection.Find(What:="3", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
there are going to be multiple operations. each time excel is going to order the column d from least to small, it is then going to find the first 3 then clear the contents in every cell in c above the first cell in d that had a 3 in it. i can't figure out how to select the cells in c that will be above that first row that has a 3 in d. here is the complete code if you're interested.
Code:
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Range("D2").Select
ActiveCell.FormulaR1C1 = "=LEN(RC[-1])"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D10485")
Columns("D:D").Select
Selection.copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.Replace What:="0", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("A:M").Select
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("current").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("current").Sort.SortFields.Add Key:=Range( _
"D2:D18627"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("current").Sort
.SetRange Range("A1:M18627")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("D:D").Select
Selection.Find(What:="3", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate