Trying to simplify data in Excel through VBScript
Solved/Closed
ColGood
Posts
2
Registration date
Monday May 1, 2017
Status
Member
Last seen
May 5, 2017
-
Updated on May 1, 2017 at 02:45 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - May 8, 2017 at 11:00 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - May 8, 2017 at 11:00 AM
Related:
- Trying to simplify data in Excel through VBScript
- Transfer data from one excel worksheet to another automatically - Guide
- Number to words in excel - Guide
- How to take screenshot in excel - Guide
- Tmobile data check - Guide
- Dd-mm-yyyy format in excel - Guide
1 response
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
May 4, 2017 at 11:01 AM
May 4, 2017 at 11:01 AM
Hi ColGood,
Give the following code a try:
How to implement and run a code:
- From Excel hit Alt + F11 to open the “Microsoft Visual Basic” window.
- Go to the top menu in the newly opened window > Insert > Module.
- Paste the code in the big white field.
- You can now close this window.
- Back at Excel, hit Alt + F8 to display the available macro’s.
- Double-click the macro you wish to run.
NOTE: macro’s cannot be reversed using the blue arrows. Always make sure you save your file (or create a back up to be entirely sure) before running a code, so you can re-open your file if something unforeseen happens or you want to go back to the situation before the code was run.
Best regards,
Trowa
Give the following code a try:
Sub RunMe()
Dim x As Integer
x = 1
Do
If Range("D" & x).Value <> vbNullString Then
x = x + 1
Rows(x).Insert
Range("A" & x).Value = Range("A" & x - 1).Value
Range("B" & x).Value = Range("B" & x - 1).Value
Range("C" & x).Value = Range("D" & x - 1).Value
End If
If Range("E" & x - 1).Value <> vbNullString Then
x = x + 1
Rows(x).Insert
Range("A" & x).Value = Range("A" & x - 1).Value
Range("B" & x).Value = Range("B" & x - 1).Value
Range("C" & x).Value = Range("E" & x - 2).Value
End If
x = x + 1
Loop Until Range("A" & x) = vbNullString
Columns("D:E").ClearContents
End Sub
How to implement and run a code:
- From Excel hit Alt + F11 to open the “Microsoft Visual Basic” window.
- Go to the top menu in the newly opened window > Insert > Module.
- Paste the code in the big white field.
- You can now close this window.
- Back at Excel, hit Alt + F8 to display the available macro’s.
- Double-click the macro you wish to run.
NOTE: macro’s cannot be reversed using the blue arrows. Always make sure you save your file (or create a back up to be entirely sure) before running a code, so you can re-open your file if something unforeseen happens or you want to go back to the situation before the code was run.
Best regards,
Trowa
May 5, 2017 at 12:54 PM
May 8, 2017 at 11:00 AM