vba select cells

[kylefoley76]

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.

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

 

[AlphaFrog]

As best I can tell, you want to delete all rows where the item in column C has a length less than 3. Is that correct?

If yes, try something like this (test it on a copy of your data)...

    Dim Lastrow As Long
    
    ' Last used row on the sheet
    Lastrow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    Application.ScreenUpdating = False

    Columns("D:D").Insert Shift:=xlToRight
    Range("D2").Formula = "=IF(ISBLANK(C2),"""",LEN(C2))"
    Range("D2").AutoFill Destination:=Range("D2:D" & Lastrow)
    Range("D1").Value = "Temp Header"
    
    ActiveSheet.AutoFilterMode = False
    Range("D1").AutoFilter Field:=4, Criteria1:="<3", Operator:=xlAnd
    On Error Resume Next
        Range("D2:D" & Lastrow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    On Error GoTo 0
    
    ActiveSheet.AutoFilterMode = False
    Columns("D:D").Delete
    Application.ScreenUpdating = True

 

[kylefoley76]

no, i don't want to delete rows, i want to clear contents in column c, if column d reads >3.

in any case the idea of going down until you hit a certain number than selecting all cells from the beginning until that number is a skill that i'm going to be using again and again and would really like to have.

 

[AlphaFrog]

There are different ways it could be done. Try something like this...

Range("D2", ActiveCell).Select