Pasting non-empty array values into Excel

Question

I'm using the code below to loop through a array(populated from a range) and then add the value of the cell to another array, then I paste the new array into a table in Excel. It's working fine but it also pastes all the empty array values into the table as well, this is a problem as I have a drop down list using that table and it contains lots of empty values at the end.

Is there a way I can either remove the empty values from the array or only paste the values which aren't empty?

Sub newFilterStaff()

Dim sourceData, targetData() As Variant
Dim sourceRange, targetRange, rng As Range
Dim sheet As Worksheet
Dim i, staffCount As Integer
Dim time As Long
Dim name As String

time = GetTickCount

'Set default values
staffCount = 0
Set sheet = Worksheets("wfm_staff")

Set sourceRange = sheet.[C1:C100]
sourceData = sourceRange.Value

'sheet.Range("E2:E50").Clear 'Clear previous list

For i = LBound(sourceData, 1) To UBound(sourceData, 1)

    If sourceData(i, 1) <> "XXXX" And i <> 1 Then 'Remove header row

    Set rng = sheet.Range("A" & i)

    With rng
    name = .Value
    End With

    ReDim Preserve targetData(0 To staffCount) 'Add name to array
    targetData(staffCount) = name
    staffCount = staffCount + 1

    End If

Next

Range("E2:E" & UBound(targetData) + 1) = WorksheetFunction.Transpose(targetData)

Debug.Print GetTickCount - time, , "ms"

End Sub

Are there any blanks within sourceRange or only at the end? — Gary's Student
– Gary's Student, Commented Jul 15, 2014 at 11:32

Gary's Student · Accepted Answer · 2014-07-15 11:46:29Z

1

Then just check for blanks:

Sub newFilterStaff()

Dim sourceData, targetData() As Variant
Dim sourceRange, targetRange, rng As Range
Dim sheet As Worksheet
Dim i, staffCount As Integer
Dim time As Long
Dim name As String

time = GetTickCount

'Set default values
staffCount = 0
Set sheet = Worksheets("wfm_staff")

Set sourceRange = sheet.[C1:C100]
sourceData = sourceRange.Value

'sheet.Range("E2:E50").Clear 'Clear previous list

For i = LBound(sourceData, 1) To UBound(sourceData, 1)

    If sourceData(i, 1) <> "XXXX" And i <> 1 Then 'Remove header row
        If sourceData(i, 1) <> "" Then

            Set rng = sheet.Range("A" & i)

            With rng
                name = .Value
            End With

            ReDim Preserve targetData(0 To staffCount) 'Add name to array
            targetData(staffCount) = name
            staffCount = staffCount + 1
        End If
    End If

Next

Range("E2:E" & UBound(targetData) + 1) = WorksheetFunction.Transpose(targetData)

Debug.Print GetTickCount - time, , "ms"

End Sub

answered Jul 15, 2014 at 11:46

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Harry12345 Over a year ago

Also had to change Range("E2:E" & UBound(targetData) + 1) to Range("E2:E" & staffCount + 1)

Gary's Student Over a year ago

You might also consider using Copy/Paste rather than the array-to-array transfer.

Harry12345 Over a year ago

I thought about that, but I assumed just copying it to the array then pasting the whole array would be a faster way of doing it

Karthick Gunasekaran · Accepted Answer · 2014-07-15 11:44:17Z

0

If name <> "" Then
        targetData(staffCount) = name
        staffCount = staffCount + 1
End If

answered Jul 15, 2014 at 11:44

Karthick Gunasekaran

2,7231 gold badge17 silver badges26 bronze badges

Collectives™ on Stack Overflow

Pasting non-empty array values into Excel

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related