deleting massive rows from excel

Question

I have a big csv file opened in EXCEL. I would like to delete all the rows from row 100020 to row 800030. I tried selecting all the rows using mouse, but not quite easy. Are there any easy approaches to do this kind of deleting operation?

highlight row 100020. scroll down using mouse wheel/scroll bar (not cursor keys - the highlighting should not vanish) to row 800030, and then SHIFT+click to highlight this row and all rows in between — SeanC, Nov 12 '13 at 21:17

Automate This · Accepted Answer · 2013-11-12T21:23:01.880

4

You mean like this:

Sub deleteRows()
  Rows("100020:800030").Delete Shift:=xlUp
End Sub

Place the code in new module file like this:

enter image description here

edited Nov 12 '13 at 21:23

answered Nov 12 '13 at 21:15

Automate This

30,726
11
60
82

yes, that should be the one that I would like to do. But where to input this kind of command line? Could you elaborate more? – user288609 Nov 12 '13 at 21:17
This is VBA macro in excel. From developer tab, select VBA and input this into a new module. Then run (F5). If you don't have the developer tab goto file, options, customize ribbon and check developer. – Automate This Nov 12 '13 at 21:19
+ 1 Nicely explained. :) – Siddharth Rout Nov 13 '13 at 05:09

nsg · Answer 2 · 2013-11-12T21:49:22.760

3

Hit Ctrl+G (Go To), In "Reference" box enter $100020:$800030 and hit enter. This selects the rows. Then do what you need with them: delete, copy, etc.

edited Nov 12 '13 at 21:49

answered Nov 12 '13 at 21:26

nsg

539
1
6
18

I get 'Reference is invalid', Excel 2010, how are you entering it exactly? – Automate This Nov 12 '13 at 21:32
@PortlandRunner: hm, I also use Excel 2010. Does something simpler, like "$2:$5" work? Or "2:5"? Or "B2:C3"? Also, what is your default reference style, A1 or R1C1? – nsg Nov 12 '13 at 21:43
Smaller numbers work fine. Looks like anything over 65536 (2^16) is considered invalid for me. My default reference style is A1. Interesting... – Automate This Nov 12 '13 at 21:59
1

Found it... I was in compatibility mode for 07' due to the document I had opened, so the workbook limited my rows. – Automate This Nov 12 '13 at 22:02
+ 1 For the non vba method :) – Siddharth Rout Nov 13 '13 at 05:11

score -1 · Answer 3 · answered Nov 12 '13 at 21:18

-1

If you have a raw text csv that you imported into Excel, this'll work faster with sed (if you have access to a system with sed):

sed '100020,800030d' my.csv > my_new.csv

answered Nov 12 '13 at 21:18

cbmanica

3,502
7
36
54

deleting massive rows from excel

3 Answers3