Deleting Rows:

[Bruce2000]

Have 100,000 rows of data.

How can I delete, for example, all rows below row #20,000?
Without doing it manually.

Thanks,
Bruce

 

[Smitty]

Something like this maybe:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>        i = Application.InputBox("From what row onward do you want to delete?", "Delete Rows", 2, Type:=1)<br>        <br>        lr = Cells(Rows.Count, "A").End(xlUp).Row<br>        <br>        Range(Cells(i, "A"), Cells(lr, "A")).EntireRow.Delete<br>        <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[Bruce2000]

?? Over my head Smitty.
Do I put this as a formula into a cell? What would it look like.

 

[Smitty]

That's VBA (Visual Basic for Applications) code.

Hit ALT+F11 to open the VBE (Visual Basic Editor), then goto Insert-->Module and paste the code into the window that opens on the right. ALT+Q will exit you back to Excel.

You can then use ALt+F8 to open the Macro window, select "Foo" and Run to execute the code. If it does what you want you can draw a button on the sheet and assign the macro to it.

Note to try this on a COPY of your workbook, as code isn't undoable unless you close without saving.

Here's a brief overview of VBA, starting with the Macro Recorder.

 

[Rick Rothstein]

?? Over my head Smitty.
Do I put this as a formula into a cell? What would it look like.

As Smitty said, it is VBA code. The reason he gave you that is because you said "Without doing it manually"... there is no formula that will physically delete values (the formula itself is a value, even if you have it return the empty string "") and, on top of that, in general, formulas cannot make physical changes on a worksheet except to return a value to the cell they are in. So, the only way to do what you asked for without doing something manual is to have VBA code do it for you.

 

[Rick Rothstein]

Something like this maybe:

Sub foo()
Dim i As Long, lr As Long

i = Application.InputBox("From what row onward do you want to delete?", "Delete Rows", 2, Type:=1)

lr = Cells(Rows.Count, "A").End(xlUp).Row

Range(Cells(i, "A"), Cells(lr, "A")).EntireRow.Delete

End Sub

HTH,

Just in case the last piece of data is not in Column A, I would use this in place of the code line I highlighted in red...

lr = Cells.Find(What:="*", SearchOrder:=xlRows,  SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row