MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

delete multiple rows

  Asked By: Ken    Date: Nov 03    Category: MS Office    Views: 860

I want to delete 30 rows from a database. I have written code to find
the first line that I want to delete by incrementing a variable -
firstrow - until the data I'm looking for is found. I can then loop 30
times to delete rows(firstrow), but I believe it would be faster to
name a range of the thirty rows beginning with firstrow and then delete
all thirty rows at once. Am I right? Can someone tell me how to do



5 Answers Found

Answer #1    Answered By: Sophia Campbell     Answered On: Nov 03

Yes it probably would be faster, but you probably don't need to name a
range - post your code  and it'll probably get tweaked for you.

Answer #2    Answered By: Andrew Brown     Answered On: Nov 03

Before we try to reply to the question can you confirm that the "database" you
are referring to is an excel spreadsheet or a list on the spreadsheet. If it
is a SQL or Access database  you should ask an appropriate list..

In general terms, in excel, you could define a range  comprising the thirty
rows and then use delete  complete row.

Answer #3    Answered By: Gustavo Costa     Answered On: Nov 03

Sorry I wasn't clear. I am working with a list of data  in an excel
spreadsheet. As I described, my code  finds the first row that I want
to delete  by increasing the value of the variable  "firstrow" until it
finds a name I am looking for in the first column. Then it sets a
counter at 1 and increases it to 30, eliminating rows(firstrow)
before each increment. The code follows:

firstrow= 2
ActiveWorkbook.Sheets("Data").Cells(2, 1).Select
Do While Cells(firstrow, 1).Value <> frmNameSelect.NametoCut.Value
firstrow = firstrow + 1
For i = 1 to 30
Selection.Delete Shift:=xlUp
Next i

But I don't know how to delete the set of rows  beginning
with "firstrow" other than one at a time.

Thanks for the help.

Answer #4    Answered By: Tommy Thompson     Answered On: Nov 03

Try this

Private Sub Del30Rows()
Dim x
x = 2
ActiveSheet.Rows(x & ":" & x + 29).EntireRow.Delete

End Sub

Answer #5    Answered By: Adelmo Fischer     Answered On: Nov 03

I'm not sure if this helps but let we try;

Dim FirstRow As Integer
Dim LastRow As Integer 'I assume that you find it before
ActiveSheet.Rows(FirstRow & ":" & LastRow).Delete

Didn't find what you were looking for? Find more on delete multiple rows Or get search suggestion and latest updates.