select cell in random location and delete vba

Question

I have only started coding a few months ago. I am trying to arrange random numbers in a column in descending order. In the route I've chosen to go, I'm trying to find a simple code to select a cell with a specific value (the minimum number in the column), regardless of it's location in column.

Thank you in advance.

`I am trying to arrange random numbers in a column in descending order` Look into `.Sort`. To find the smallest number in a range, and delete it use `.Find` — Siddharth Rout, Nov 30 '17 at 19:45
Please read> [How to Ask](https://stackoverflow.com/help/how-to-ask) — , Nov 30 '17 at 20:24
@SamanthaTonog If my answer works as you asked please accept it by hitting the button below the up/down vote! Thank you :) — Teasel, Jan 25 '18 at 16:49

Teasel · Answer 1 · 2017-11-30T20:30:53.547

You can use the Sort function to sort your column by ascending or descending order.

Here is a code to order Column A as an example:

Sub OrderColumnDescending()
    Dim lastrow As Long

    'If you change the column change the 1 with the number
    'of your column to order
    lastrow = Cells(Rows.Count, 1).End(xlUp).Row

    Range("A1:A" & lastrow).Sort key1:=Range("A1:A" & lastrow), _
        order1:=xlDescending, Header:=xlNo
End Sub

Since you said you're new to VBA, here are some explanations about the code.

lastrow = Cells(Rows.Count, 1).End(xlUp).Row

This line get the last used row in the Column number 1, I put the value in the lastrow variable so I can use it later. It's better than fixed values and more readable than writting the whole thing everytime.

Range("A1:A" & lastrow).Sort key1:=Range("A1:A" & lastrow), _
        order1:=xlDescending, Header:=xlNo

I create a Range object containing cells from A1 to AX where X is the last row used in column A that we calculated earlier. From this Range I call its Sort method and give its Key1, Order1 and Header parameters.

Here are useful links about the Range.Sort method.

MSDN - Range.Sort Method

Stack Overflow - VBA Excel sort range by specific column

select cell in random location and delete vba

1 Answers1