Скачать или смотреть How to Repeat Values Multiple Times Using Excel VBA

Excel How to Repeat Values Multiple Times Using VBA

If you have certain values on your table in which you want to repeat several times, doing this manually is truly a very tedious job and may cause errors when doing the data entry. This task is relatively easy using VBA. The code is given to you in the description box. In any case that the code can’t be entered in the description box, you may pause the video where the code is displayed and copy it in the module box. Please watch the video to know how this technique can be applied.

1. Press Alt + F11 to go to Visual Basic Editor
2. Insert Module
3. Copy and paste the below code in the module
---------------------------------------------------------

Sub RepeatValues()

Dim x As Range, SelectedRange As Range, OutputRange As Range

Range("E:F").ClearContents
Range("E1:F1").Value = [{"Result","Sorted Data"}]

Set SelectedRange = Application.Range("A2", Range("A2").End(xlDown).End(xlToRight))
Set OutputRange = Application.Range("E2")

For Each x In SelectedRange.Rows
Occurrence = x.Range("A1").Value
xNumber = x.Range("B1").Value
OutputRange.Resize(Occurrence, 1).Value = xNumber
Set OutputRange = OutputRange.Offset(Occurrence, 0)
Next

Range("E2:F" & Range("E" & Rows.Count).End(xlUp).Row).FillRight
Range("F1", Range("F1").End(xlDown)).Sort Key1:=Range("F1"), Order1:=xlAscending, Header:=xlYes

MsgBox "A new list has been created in E column with data repeated multiple times and sorted data in F column.", vbOKOnly, "Excel by Romeo Costillas"

End Sub

---------------------------------------------------------

4. Insert command button and assign macro
5. Apply macro
6. Save the file as Macro-enabled file

"Don’t get discouraged & discard the efforts, every repetition makes us better. If you keep repeating something with determination, you will learn the process eventually, even the reason why it isn’t working for you."
― Shahenshah Hafeez Khan

*************************************************
Want to learn more about Excel?

SUBSCRIBE NOW:
   / @romeocostillas  

YouTube Playlist:
   • Excel Keyboard Shortcut Control Combinatio...  

Or visit me on Facebook at   / excelbyromeocostillas  

See you there!

Romeo Costillas
Microsoft Certified Solutions Associate BI Reporting
Microsoft Office Specialist Master
Microsoft Office Specialist Excel Expert


"To EXCEL in the workplace, one must know how to use excel!"
Romeo

Tagalog Excel | Filipino | Pinoy Excel | Excel Tutorial | Excel Tagalog Tutorial

#excel #microsoftexcel #excelbyromeocostillas #excelonline #exceltraining #excelfreetraining #excelmasterclass #tagalogexcel #excelpinoy