1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA to copy data minus blanks

Discussion in 'VBA Macros' started by Kellis, Mar 12, 2018.

  1. Kellis

    Kellis Member


    Looking for help, I have code to copy a data range from the Input sheet to the last row used on the data sheet. The problem I have is my code picks up the blank (formula)cells and pastes them to meaning I have gaps in the data.

    My question is how can I copy the range without the blank rows.

    Please see attached

    Any help is much appreciated


    Attached Files:

  2. Marc L

    Marc L Excel Ninja


    Hi !

    You can use a filter to remove blanks ...​
  3. Hui

    Hui Excel Ninja Staff Member

    I would cleanup the Data Worksheet by adding a few rows of code as shown below

    Code (vb):
    Sub Import_Data()

    'Copy and paste Agents data from Import data Sheet
    Application.ScreenUpdating = False
        Sheets("Import Data").Select
        Range(Selection, Selection.End(xlDown)).Select

        Range("A" & Rows.Count).End(xlUp).Offset(1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

        'Cleanup data worksheet
       Dim lr as Integer
        lr = Range("A" & Rows.Count).End(xlUp).Row
        For i = lr To 2 Step -1
          If Cells(i, 1) = "" Then Rows(i).Delete
        Next i

        Application.CutCopyMode = False
        Application.ScreenUpdating = True
    End Sub
    Kellis likes this.
  4. Kellis

    Kellis Member

    Hi Hui,

    Thanks a lot this is great.

Share This Page