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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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

M
  • Question Question
Operational Risk Methodology on VBA
Replies
0
Views
81
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
191
krunal123
K
JeffGrant
  • Solved
Array output - very very strange - I am a complete loss
Replies
4
Views
515
Peter_SSs
Peter_SSs
I
  • Question Question
Listbox not being populate when code is run
Replies
4
Views
643
ipbr21054
I
MyHanhCB
  • Solved
Sort array .!
Replies
8
Views
549
MyHanhCB
MyHanhCB

Forum statistics

Threads
1,223,891
Messages
6,175,229
Members
452,621
Latest member
Laura_PinksBTHFT

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