Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete Rows (2003)

    I am trying to delete all rows except the header row that = report in column A. I have got this far, but also contains the header.

    Option Explicit

    Sub DeleteHeadersExceptMain()

    Dim r As Long
    Dim n As Long
    Dim rng1 As Range

    n = Range("A65536").End(xlUp).Row

    Set rng1 = Range("A2:A" & n)

    For r = n To 1 Step -1
    If Trim(Range("A" & r)) = "Report" Then
    Range("A" & r).EntireRow.Delete
    End If
    Next r
    End Sub

    Any pointers please...

    Thanks

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Delete Rows (2003)

    If the header row is row 1 you could use:

    For r = n To <font color=red>2</font color=red> Step -1

    If the header row must be determined at runtime, how should the code know what the header row is?

    Steve

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Rows (2003)

    Thanks Steve, that works:
    I'm not sure that I understand your question. The reason that I needed this is so that I can lose the duplicate report headers in a worksheet, except the top one. I run a .batch file that copies 8 files into one and output's to excel, hence duplicate non required headers.

    Thanks again
    Nathan

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Rows (2003)

    The For.. Next that Steve is suggesting leaves row 1 untouched.

    What he is asking is, if the header row is not always in row 1, is there something unique about the top header row that would set it apart from the rest of the header rows.

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Rows (2003)

    There will always be a header in row 1, being the header of the first of the eight reports. That will be the only one required.

    Thanks

    Nathan

Posting Permissions

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