VBA to copy data minus blanks

[Kellis]

Hi,

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

Regards.

 

[Marc L]

Hi !

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

 

[Hui]

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

Sub Import_Data()

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

    Sheets("Data").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]

Hi Hui,

Thanks a lot this is great.