CODEWITHSUNDEEP
excelvba
Don Nalaka
Don Nalaka

Reputation: 139

How to exclude first row when using range and array function?

I'm using below VBA to copy past data from an array into a sheet "FD". However, it copies the first row even I define my range to start with second row.

This is how I define the range

arr = TB.Range("AD2:AK2").CurrentRegion.Value2

How do I ignore the first row here ?

Function copyTBdata()

Dim TB As Worksheet, fd As Worksheet
Set TB = ThisWorkbook.Worksheets("TB")
Set fd = ThisWorkbook.Worksheets("FD")
 
    
Dim arr As Variant

arr = TB.Range("AD2:AK2").CurrentRegion.Value2
 

Dim i As Long, J As Long
Dim row As Long, column As Long
row = 240
For i = LBound(arr) To UBound(arr)

    
If arr(i, 2) <> "NULL" Then
        
        ' Copy each column
        For J = LBound(arr, 2) To UBound(arr, 2)
            fd.Cells(row, J).Value2 = arr(i, J)
        Next J
        
        ' move to the next output row
        row = row + 1
        
    End If
    
Next i

End Function

Upvotes: 1

Views: 847

Answers (1)

Jeremy Thompson
Jeremy Thompson

Reputation: 65544

To iterate over the Array/Range and start on the 2nd row, you can use a + 1:

For i = LBound(arr) + 1 To UBound(arr)

Pro tip get in the habit of using constants instead of magic numbers.

Private Const secondRow as Interger = 1

...

For i = LBound(arr) + secondRow To UBound(arr)

Upvotes: 1

Related Questions