+ Reply to Thread
Results 1 to 2 of 2

Loop through a range pasting in Blank Cells

Hybrid View

  1. #1
    Registered User
    Join Date
    05-21-2011
    Location
    Maryland
    MS-Off Ver
    Excel 2007
    Posts
    3

    Loop through a range pasting in Blank Cells

    I have a macro that will copy and paste to the next blank cell, but I need it to loop through the entire worksheet, with the condition that it skips the header rows in gray. In the attached sample worksheet I am tryting to copy E8:H8 to all blank cells to bottom, skipping row 21 & 24....etc. My macro copies and pastes correctly to Blank but i haven't figure out to make it loop and skip header properly.

    Sub CopyH8ToBlanks()
    
       Range("E8:H8").Activate
       Selection.Copy
    
       lMaxRows = Cells(Rows.Count, "E").End(xlUp).Row
       Range("E" & lMaxRows + 1).Select
       Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
          False, Transpose:=False
    
    End Sub
    Attached Files Attached Files
    Last edited by Richard Buttrey; 05-29-2011 at 01:22 PM. Reason: Adding code tags for new member

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Loop through a range pasting in Blank Cells

    Hi

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

    Please note the above. As you are relatively new here I'll edit your post for you on this occasion.

    Rather than looping through cells which is always more time consuming, try filtering data first. I don't know any quicker method for changing blocks of data. So something like

    Sub CopyH8ToBlanks()
        Dim lLastRow As Long
        lLastRow = Range("A" & Rows.Count).End(xlUp).Row
        Range("C6").AutoFilter Field:=3, Criteria1:="<>"
        Range("E8:H8").Copy Destination:=Range("E8:E" & lLastRow)
        Cells.AutoFilter
    End Sub
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1