ruannpreger
New Member
- Joined
- Sep 25, 2020
- Messages
- 8
- Office Version
- 365
- Platform
- Windows
Hi,
I would like to make the VBA below more efficient.
In my simplified example i have 5 rows populated.
Column D has 5 values; lets say
D1 = AAA
D2 = BAH
D3 = KLM
D4 = TRE
D5 = BHG
so vba counts the number of rows ( which is 5) and then copy value in D1 ( which is AAA) 5 times in col H, next it copies values D2 ( BAH) 4 times to col i.
by changing the vba manually, it works however in real i have over 100 values.
Now i would like to have this / everything only in col H underneath eachother and preferably more efficient.
it should result in :
Col H
AAA
AAA
AAA
AAA
AAA
BAH
BAH
BAH
BAH
KLM
KLM
KLM
thanks.
I would like to make the VBA below more efficient.
In my simplified example i have 5 rows populated.
Column D has 5 values; lets say
D1 = AAA
D2 = BAH
D3 = KLM
D4 = TRE
D5 = BHG
so vba counts the number of rows ( which is 5) and then copy value in D1 ( which is AAA) 5 times in col H, next it copies values D2 ( BAH) 4 times to col i.
by changing the vba manually, it works however in real i have over 100 values.
Now i would like to have this / everything only in col H underneath eachother and preferably more efficient.
VBA Code:
Sub test()
Dim MyCount As Long
MyCount = Range("A" & Rows.Count).End(xlUp).Row
Dim MyVal As String
If MyCount > 0 Then MyVal = Range("d1").Text
Range("h1:h" & MyCount) = MyVal
MyCount2 = Range("A" & Rows.Count).End(xlUp).Row - 1
Dim MyVal2 As String
If MyCount2 > 0 Then MyVal2 = Range("d2").Text
Range("i1:i" & MyCount2) = MyVal2
MyCount3 = Range("A" & Rows.Count).End(xlUp).Row - 2
Dim MyVal3 As String
If MyCount3 > 0 Then MyVal3 = Range("d3").Text
Range("j1:j" & MyCount3) = MyVal3
End sub
it should result in :
Col H
AAA
AAA
AAA
AAA
AAA
BAH
BAH
BAH
BAH
KLM
KLM
KLM
thanks.