CODEWITHSUNDEEP
excelvba
Antillar Maximus
Antillar Maximus

Reputation: 227

Excel Macro for copying specific rows

I have a huge spreadsheet of about 300001 rows and 20 columns. The first column A is a repetition of numbers from 0 to 3 in steps of 0.001 (3001 cells, 100 repetitions in total). I would like to pick out ROWS corresponding to the column A entry of 3, transpose them and write them to a new sheet. How do I do this? Total noob at macros and VBA but I am familiar with programming (mostly C++).

Thanks!

Upvotes: 0

Views: 1118

Answers (1)

Stepan1010
Stepan1010

Reputation: 3136

You don't really need macros to do something like this - but then again this seems more like something you are doing for fun than for anything practical.

You could just sort your range "A1:T300001" by column A from high to low and copy and paste the top hundred rows.

But if you want a macro that does this, this should work for you:

Sub MacroAutofilterExample()
    ActiveSheet.Range("$A$1:$T$300001").AutoFilter Field:=1, Criteria1:="3.000"

    Range("A1:T300001").Select
    Selection.Copy
    Sheets(2).Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select

    Sheets("Sheet1").Select
    Range("A1").Select
    Selection.AutoFilter
    Range("A1").Select

End Sub

Obviously their are some efficiency losses by using selections(since I recorded parts of it) but you can change those to use set ranges - generally this should give you an idea.

EDIT:

You would change the field: 

Transpose:=False

In the above code to:

Transpose:=True

If you wanted your results transposed.

Upvotes: 3

Related Questions