vba select cells

kylefoley76

Well-known Member
Joined
Mar 1, 2010
Messages
1,550
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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384
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)...
Code:
    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

Well-known Member
Joined
Mar 1, 2010
Messages
1,550
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384
There are different ways it could be done. Try something like this...

Code:
Range("D2", ActiveCell).Select
 

Watch MrExcel Video

Forum statistics

Threads
1,123,488
Messages
5,601,974
Members
414,487
Latest member
inxlsplot

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top