Deleting the first 4 rows of a range using a macro (Excel 2000 SR1)

Topic

New Reply

Can anyone give me a macro command that would delete the first 4 rows of a selected range. I’m working on a macro that pastes text into a spreadsheet and names the selected range…next I need it to delete the first 4 rows of that selected range.

Thanks in advance.

Christa

Reply | Quote

Replies

If you want to delete the entire row, i.e. incled all cells outside the range as well as in you could use the following :

Sub Del4Rows()
    Dim r As Integer
    Range("RangeX").Cells(1).Select
    For r = 1 To 4
        Selection.EntireRow.Delete
    Next
End Sub

Otherwise, the following will delete only the cells in the first 4 rows of the actual range.

Sub Del4Rows2()
    Dim iCols As Integer
    Dim r As Integer, c As Integer
    iCols = Range("RangeX").Columns.Count
    For r = 1 To 4
        For c = 1 To iCols
            Range("RangeX").Cells(r, c).Delete
        Next
    Next
End Sub

In both cases the range name is RangeX. which you will have to change to suit your purposes.

Andrew C

Reply | Quote

If you have the range selected as you stated, then to delete the first four rows is just a one-liner:

Selection.Rows("1:4").Delete xlShiftUp

Each of he Rows, Columns and Cells properties can be applied to a range and now the indicies are offsets to the begining of the range. HTH –Sam

Reply | Quote