Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Guy Havers
    Guest

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

    Hi Christa

    Assuming your range is selected, the following will change the selection to the first four rows:

    Selection.Resize(4, Selection.Columns.Count).Select

    Then I'm not quite sure what you want to do, but one of the following should do the job.

    a) Selection.ClearContents
    [img]/forums/images/smilies/cool.gif[/img] Selection.Delete
    c) Selection.EntireRow.Delete

    Hope this works!

    Guy

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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 :<pre>Sub Del4Rows()
    Dim r As Integer
    Range("RangeX").Cells(1).Select
    For r = 1 To 4
    Selection.EntireRow.Delete
    Next
    End Sub</pre>

    Otherwise, the following will delete only the cells in the first 4 rows of the actual range.<pre>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</pre>

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

    Andrew C

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    If you have the range selected as you stated, then to delete the first four rows is just a one-liner:
    <pre>Selection.Rows("1:4").Delete xlShiftUp</pre>


    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
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •