2

please help..i need a macro to delete specific rows based on row number (line number) for example, if i want to delete the the 2nd,9th,20th,150th rows, how it can be done in macro, please provide a macro where i can copy and paste the line numbers in the code and run from module. i have the row numbers in sheet2 Column A which are the rows to be deleted from sheet1

pnuts
  • 58,317
  • 11
  • 87
  • 139
davidb
  • 263
  • 5
  • 10
  • 23

1 Answers1

2

for one row:

Rows(4).Delete Shift:=xlUp

For multiple rows:

Union(Rows(4), Rows(7)).Delete Shift:=xlUp

For your specific case to allow to dynamically delete rows based on a list of row numbers in a source sheet. Change SourceWks to the worksheet where the numbers are stored and deletedWks to the worksheet where the rows are going to be deleted.

Dim deleteRows As Range
Dim data() As Variant
Dim i As Double

Dim SourceWks As Worksheet, deleteWks As Worksheet

Set SourceWks = Sheet2
Set deleteWks = Sheet1

    With SourceWks
        data = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
    End With

    Set deleteRows = deleteWks.Rows(data(1, 1))

    For i = 2 To UBound(data, 1)

        Set deleteRows = Union(deleteRows, deleteWks.Rows(data(i, 1)))

    Next i

    deleteRows.Delete Shift:=xlUp
InContext
  • 2,461
  • 12
  • 24
  • hi philip A Barnes, im zero in vba, this works fine, but i have many specific rows, how do i do it? kindly help – davidb Dec 14 '12 at 12:04
  • @davidb: if you have many specific rows then use the second code sample where it deletes rows 4 and 7. Extend and change it how you want. – InContext Dec 14 '12 at 12:09
  • i have the row numbers which i want to delete in sheet2 column A, it is really time consuming entering one by one in the code, can u give me a code which reads the row numbers from sheet2 and delete the mentioned rows from sheet1?? i have the row numbers in sheet2 column A – davidb Dec 14 '12 at 12:31
  • I have updated my answer to include your above refinement. – InContext Dec 14 '12 at 12:49
  • your are great. it works like the way i wanted.thanks a lot. – davidb Dec 14 '12 at 12:59
  • great. Also its good practise to accept the answer you prefer so others know it has been completed. – InContext Dec 14 '12 at 13:16