Copy UsedRange except for Header
Results 1 to 3 of 3

Thread: Copy UsedRange except for Header
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2011
    Posts
    409
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Copy UsedRange except for Header

    In VBA how can I copy a Used Range except for the header?

  2. #2
    Board Regular
    Join Date
    Oct 2007
    Posts
    5,807
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Copy UsedRange except for Header

    Try this. It copies the used range except for the header row from Sheet1 to Sheet2.
    Code:
    Sub CopyUsedRange()
        
        Sheets("Sheet2").Cells.ClearContents
        
        With Sheets("Sheet1").UsedRange
            .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0).Copy Sheets("Sheet2").Range("A1")
        End With
        
    End Sub

  3. #3
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,112
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Copy UsedRange except for Header

    Here's another one.

    Code:
    Dim rng
    Set rng = Worksheets("Sheet1").UsedRange
    Intersect(rng, rng.Offset(1)).Copy Worksheets("Sheet2").Range("A2")
    If posting code please use code tags.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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