How to make a "fill down" inside a 2D VBA array?

F

Fractalis

Active Member
Joined
Oct 11, 2011
Messages
328
Office Version
  1. 365
Platform
  1. Windows
Hi to all,

I have an array like this below, for which I'd like to have a "fill down" like for first 2 columns. I know there is a way in a sheet doing "RANGE(myRng).FillDown",
but how can be done manipulating the VBA array itself? Thanks for any help.

VBA Code: 
Dim arr(1 To 11, 1 To 4) As Variant

arr(1, 1) = "ME01": arr(1, 2) = "B": arr(1, 3) = "55":  arr(1, 4) = ""
arr(2, 1) = "": arr(2, 2) = "": arr(2, 3) = "51":   arr(2, 4) = ""
arr(3, 1) = "": arr(3, 2) = "": arr(3, 3) = "1784": arr(3, 4) = ""
arr(4, 1) = "R03":  arr(4, 2) = "KD":   arr(4, 3) = "359":  arr(4, 4) = ""
arr(5, 1) = "": arr(5, 2) = "": arr(5, 3) = "36":   arr(5, 4) = ""
arr(6, 1) = "": arr(6, 2) = "Y": arr(6, 3) = "": arr(6, 4) = "M"
arr(7, 1) = "": arr(7, 2) = "": arr(7, 3) = "": arr(7, 4) = "W"
arr(8, 1) = "": arr(8, 2) = "A": arr(8, 3) = "": arr(8, 4) = "W"
arr(9, 1) = "": arr(9, 2) = "": arr(9, 3) = "": arr(9, 4) = "R"
arr(10, 1) = "": arr(10, 2) = "": arr(10, 3) = "": arr(10, 4) = "E"
arr(11, 1) = "": arr(11, 2) = "": arr(11, 3) = "": arr(11, 4) = "E"

If we see it in tabular form, the array looks like this:

ME01B55
51
1784
R03KD359
36
YM
W
AW
R
E
E


and I'd like the output array to look like this (but without print it to a range):

ME01B55
ME01B51
ME01B1784
R03KD359
R03KD36
YM
YW
AW
AR
AE
AE
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
Instead of this
Code: 
arr(2, 1) = ""
make it this
Code: 
arr(2, 1) = "ME01"
Same for all the other you fill with empties.

And you say you "fill down" the first 2 columns but you do not. Column 1 only goes to the amount of non empties in the third column.
 
Upvote 0
This is not a "Fill Down" but the result is what you showed in your post.
Code: 
Sub Maybe()
Dim myArr(1 To 11, 1 To 4) As Variant
Dim i As Long
myArr(1, 1) = "ME01": myArr(1, 2) = "B": myArr(1, 3) = "55":  myArr(1, 4) = ""
myArr(2, 1) = "": myArr(2, 2) = "": myArr(2, 3) = "51":   myArr(2, 4) = ""
myArr(3, 1) = "": myArr(3, 2) = "": myArr(3, 3) = "1784": myArr(3, 4) = ""
myArr(4, 1) = "R03":  myArr(4, 2) = "KD":   myArr(4, 3) = "359":  myArr(4, 4) = ""
myArr(5, 1) = "": myArr(5, 2) = "": myArr(5, 3) = "36":   myArr(5, 4) = ""
myArr(6, 1) = "": myArr(6, 2) = "Y": myArr(6, 3) = "": myArr(6, 4) = "M"
myArr(7, 1) = "": myArr(7, 2) = "": myArr(7, 3) = "": myArr(7, 4) = "W"
myArr(8, 1) = "": myArr(8, 2) = "A": myArr(8, 3) = "": myArr(8, 4) = "W"
myArr(9, 1) = "": myArr(9, 2) = "": myArr(9, 3) = "": myArr(9, 4) = "R"
myArr(10, 1) = "": myArr(10, 2) = "": myArr(10, 3) = "": myArr(10, 4) = "E"
myArr(11, 1) = "": myArr(11, 2) = "": myArr(11, 3) = "": myArr(11, 4) = "E"
    For i = LBound(myArr) + 1 To UBound(myArr)
        If myArr(i, 1) = "" And myArr(i, 3) <> "" Then myArr(i, 1) = myArr(i - 1, 1)
        If myArr(i, 2) = "" Then myArr(i, 2) = myArr(i - 1, 2)
    Next i
Cells(1, 15).Resize(UBound(myArr, 1), UBound(myArr, 2)) = myArr    '<---- Comment this out
End Sub
 
Upvote 0
Solution
jolivanes said:
This is not a "Fill Down" but the result is what you showed in your post.
Code: 
Sub Maybe()
Dim myArr(1 To 11, 1 To 4) As Variant
Dim i As Long
myArr(1, 1) = "ME01": myArr(1, 2) = "B": myArr(1, 3) = "55":  myArr(1, 4) = ""
myArr(2, 1) = "": myArr(2, 2) = "": myArr(2, 3) = "51":   myArr(2, 4) = ""
myArr(3, 1) = "": myArr(3, 2) = "": myArr(3, 3) = "1784": myArr(3, 4) = ""
myArr(4, 1) = "R03":  myArr(4, 2) = "KD":   myArr(4, 3) = "359":  myArr(4, 4) = ""
myArr(5, 1) = "": myArr(5, 2) = "": myArr(5, 3) = "36":   myArr(5, 4) = ""
myArr(6, 1) = "": myArr(6, 2) = "Y": myArr(6, 3) = "": myArr(6, 4) = "M"
myArr(7, 1) = "": myArr(7, 2) = "": myArr(7, 3) = "": myArr(7, 4) = "W"
myArr(8, 1) = "": myArr(8, 2) = "A": myArr(8, 3) = "": myArr(8, 4) = "W"
myArr(9, 1) = "": myArr(9, 2) = "": myArr(9, 3) = "": myArr(9, 4) = "R"
myArr(10, 1) = "": myArr(10, 2) = "": myArr(10, 3) = "": myArr(10, 4) = "E"
myArr(11, 1) = "": myArr(11, 2) = "": myArr(11, 3) = "": myArr(11, 4) = "E"
    For i = LBound(myArr) + 1 To UBound(myArr)
        If myArr(i, 1) = "" And myArr(i, 3) <> "" Then myArr(i, 1) = myArr(i - 1, 1)
        If myArr(i, 2) = "" Then myArr(i, 2) = myArr(i - 1, 2)
    Next i
Cells(1, 15).Resize(UBound(myArr, 1), UBound(myArr, 2)) = myArr    '<---- Comment this out
End Sub
Click to expand...
Thanks for your help. You're right for column1 a better sample input is like this below:


Code: 
arr(1,1) = "ME01":    arr(1,2) = "B":    arr(1,3) = "55":    arr(1,4) = ""
arr(2,1) = "":    arr(2,2) = "":    arr(2,3) = "51":    arr(2,4) = ""
arr(3,1) = "":    arr(3,2) = "":    arr(3,3) = "1784":    arr(3,4) = ""
arr(4,1) = "R03":    arr(4,2) = "KD":    arr(4,3) = "359":    arr(4,4) = ""
arr(5,1) = "":    arr(5,2) = "":    arr(5,3) = "36":    arr(5,4) = ""
arr(6,1) = "TP":    arr(6,2) = "Y":    arr(6,3) = "":    arr(6,4) = "M"
arr(7,1) = "":    arr(7,2) = "":    arr(7,3) = "":    arr(7,4) = "W"
arr(8,1) = "RH":    arr(8,2) = "A":    arr(8,3) = "":    arr(8,4) = "W"
arr(9,1) = "":    arr(9,2) = "":    arr(9,3) = "":    arr(9,4) = "R"
arr(10,1) = "":    arr(10,2) = "":    arr(10,3) = "":    arr(10,4) = "E"
arr(11,1) = "":    arr(11,2) = "":    arr(11,3) = "":    arr(11,4) = "E"

That look like this:

ME01B55
51
1784
R03KD359
36
TPYM
W
RHAW
R
E
E


And output would look like this (I've tried with your solution and as you said, is not doing a fill down, due to my typo).
The actual data has many rows and about 7- 12 columns, for which first 4 columns need the fill down

ME01B55
ME01B51
ME01B1784
R03KD359
R03KD36
TPYM
TPYW
RHAW
RHAR
RHAE
RHAE
 
Upvote 0
Is the data in the top picture existing data in a worksheet?
You say the first four columns need filling but in your end result picture, with the green cells, you don't show that. Is that on purpose?
The 3rd column is not filled and what about the top 5 of the 4th column?

Oh, please do me a favor. Don't quote. Refer to post numbers.
 
Last edited:
Upvote 0
jolivanes said:
Is the data in the top picture existing data in a worksheet?
You say the first four columns need filling but in your end result picture, with the green cells, you don't show that. Is that on purpose?
The 3rd column is not filled and what about the top 5 of the 4th column?

Oh, please do me a favor. Don't quote. Refer to post numbers.
Click to expand...
Yes, only first 2 should be filled-down in this sample data, in actual data the first 4 columns should be filled.

I think I was able to modify your code in order to get what I looked for. Thanks for your help.

VBA Code: 
Sub test()
Dim arr(1 To 11, 1 To 4) As Variant

Dim i As Long

arr(1, 1) = "ME01": arr(1, 2) = "B": arr(1, 3) = "55":  arr(1, 4) = ""
arr(2, 1) = "": arr(2, 2) = "": arr(2, 3) = "51":   arr(2, 4) = ""
arr(3, 1) = "": arr(3, 2) = "": arr(3, 3) = "1784": arr(3, 4) = ""
arr(4, 1) = "R03":  arr(4, 2) = "KD":   arr(4, 3) = "359":  arr(4, 4) = ""
arr(5, 1) = "": arr(5, 2) = "": arr(5, 3) = "36":   arr(5, 4) = ""
arr(6, 1) = "TP":   arr(6, 2) = "Y": arr(6, 3) = "": arr(6, 4) = "M"
arr(7, 1) = "": arr(7, 2) = "": arr(7, 3) = "": arr(7, 4) = "W"
arr(8, 1) = "RH":   arr(8, 2) = "A": arr(8, 3) = "": arr(8, 4) = "W"
arr(9, 1) = "": arr(9, 2) = "": arr(9, 3) = "": arr(9, 4) = "R"
arr(10, 1) = "": arr(10, 2) = "": arr(10, 3) = "": arr(10, 4) = "E"
arr(11, 1) = "": arr(11, 2) = "": arr(11, 3) = "": arr(11, 4) = "E"

Col = 2
    For i = LBound(arr) + 1 To UBound(arr)
        For j = 1 To Col
            If arr(i, j) = "" Then arr(i, j) = arr(i - 1, j)
        Next j
    Next i

Cells(1, 15).Resize(UBound(arr, 1), UBound(arr, 2)) = arr    '<---- Comment this out

End Sub
 
Upvote 0
Good job.
Thanks for letting us know.
Sounds like you have what you need.
Good luck.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Referencing a series name that contains data linked to a cell VBA
Replies
1
Views
57
Rijnsent
Rijnsent
H
  • Locked
  • Question Question
Help referencing a series name that contains data linked to a cell VBA
Replies
1
Views
36
Fluff
Fluff
M
  • Question Question
Operational Risk Methodology on VBA
Replies
0
Views
122
Mazurofgod
M
K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
236
krunal123
K
A
  • Solved
VBA Case Error
Replies
2
Views
65
Ark68
A

Forum statistics

Threads
1,224,818
Messages
6,181,151
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top