+ Reply to Thread
Results 1 to 1 of 1

Use macro to copy groups of ceels excluding blank rows

Hybrid View

  1. #1
    Registered User
    Join Date
    01-05-2015
    Location
    Melbourne
    MS-Off Ver
    2010
    Posts
    11

    Use macro to copy groups of ceels excluding blank rows

    I am new to this forum and am teaching myself VBA (I know I need to do a course)
    If anyone can assist me on this mater I would be appreciative

    I have a group of cells in 10 rows ("a1:j60) in my code below. .
    I copy them to another worksheet
    I then sort the destination worksheet to have the most recent entries at the top.
    The problem is that when I do not have 10 rows of data I am getting the blank lines copied to my target spreadsheet.
    The blank lines are not necessarily in order (i.e. rows 3, 5 and 8 might be blank on one update... then rows 2,& 3 on the next update).

    Is there a simple solution to not copy the blank cells or do I have to run a macro 10 times with an if command for each row?
    Any assistance will be greatly appreciated.

    Here is my code:

    'Step 1 - Update Data to TargetSheet
    ActiveWorkbook.Worksheets("DataSheet").Range("a51:j60").Copy
        With ActiveWorkbook.Worksheets("TargetSheet")
            lNextRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
            .Cells(lNextRw, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        End With
         'clear clipboard
        Application.CutCopyMode = False
    
    'Step 2 sort TargetSheet in descending order
    
        Sheets("TargetSheet").Select
        Cells.Select
        ActiveWorkbook.Worksheets("Results Archive").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Results Archive").Sort.SortFields.Add Key:=Range( _
            "A2:A5000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
            xlSortNormal
        With ActiveWorkbook.Worksheets("TargetSheet").Sort
            .SetRange Range("A1:j5000")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Range("A1").Select

    Thanks in advance
    Last edited by MikeMeerkat; 01-07-2015 at 11:54 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Rank sub groups of rows seperated with blank rows
    By peasther in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-28-2014, 05:52 PM
  2. How to create a macro to insert blank rows and copy data into blank rows?
    By zodiack101 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-29-2013, 01:18 PM
  3. [SOLVED] protecting ceels to copy -paste action
    By grigoras victor in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-19-2006, 07:36 AM
  4. protecting ceels to copy -paste action
    By grigoras victor in forum Excel General
    Replies: 0
    Last Post: 06-19-2006, 02:15 AM
  5. protecting ceels to copy -paste action
    By grigoras victor in forum Excel - New Users/Basics
    Replies: 0
    Last Post: 06-19-2006, 02:15 AM

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