Excel first timer Macro user

[ZaXaZ]

Hi, this is my first time having the need of a macro and it´s a bit overwhelming.
i started a new work and lets just say that no on here is very sharp when it comes to pc or what you can do with it most of the times.

so i get a list of car Models and types of the model in a txt doc witch most of the time is very long 100+ sometimes 10+ or 1000+. but it´s given to me in a very bad form and takes quite some time to do manual ea time..

and any one here help me with making a macro?

here is a very short example, of what i get to work with, and what i need to end up with
Model: Type
405: 101 103 121 123 201 221
900: 911 912 913 917 918 919 922 934 936 937 938
list goes one feels like its endless..

i need it to be made to
type Model
405 101
405 102
405 121
405 123
405 201
405 221

900 911
900 912
900 913
900 917
900 918
900 919
900 922
900 934
900 936
900 937
900 938

 

[ZaXaZ]

ZaXaZ,

Thanks for the new workbook.

I assume that your raw data worksheet is the first worksheet on the left in the sheets view.

After the macro in a new worksheet Results (not all rows, and, columns are shown to fit the MrExcel display area):

Excel 2007

	A	B	C	D	E	F	Y	Z	AA	AB	AC	AD
1	Model	Type		355	916		935	952		936	973
2	100	981		355	918		936	971		936	973
3	100	983		355	918		936	971
4	159	980		355	960		936	971
5	177	980		355	961		936	971
6	177	980		355	962		936	971
7	178	980		355	963		936	971
8	203	081		355	964		936	980
9	203	084		355	966		936	980
10	203	087		355	967		936	980
41	355	911		389	018		936	972
42	355	912		389	019		936	972
43	355	912		389	022		936	972
44	355	913		389	023		936	972
45	355	913		389	024		936	972
46	355	914		389	026		936	972
47	355	914		389	027		936	973
48	355	915		389	034		936	973
49	355	915		389	043		936	973
50	355	916		389	044		936	973
51

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Results​

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub ReorgDataV2()
' hiker95, 02/24/2015, ME837837
Dim w1 As Worksheet, wr As Worksheet
Dim r As Long, lr As Long, c As Range, s, i As Long, nr As Long, nlr As Long, nc As Long
Application.ScreenUpdating = False
Set w1 = Sheets(1)
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wr = Sheets("Results")
wr.UsedRange.Clear
wr.Cells(1, 1).Resize(, 2).Value = Array("Model", "Type")
With w1
  .Activate
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  With .Range("A2:B" & lr)
    .Value = Evaluate("IF(ISTEXT(" & .Address & "),TRIM(" & .Address & "),REPT(" & .Address & ",1))")
  End With
  For Each c In .Range("B2:B" & lr)
    nr = wr.Cells(.Rows.Count, "A").End(xlUp).Row + 1
    If InStr(c, " ") Then
      s = Split(c, " ")
      wr.Cells(nr, 1).Resize(UBound(s) + 1, 2).NumberFormat = "@"
      wr.Cells(nr, 2).Resize(UBound(s) + 1) = Application.Transpose(s)
      wr.Cells(nr, 1).Resize(UBound(s) + 1) = c.Offset(, -1).Value
    Else
      c.Offset(, -1).Resize(, 2).Copy wr.Cells(nr, 1)
    End If
    Application.CutCopyMode = False
  Next c
End With
With wr
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  .Range("A2:B" & lr).Sort key1:=.Range("A2"), order1:=1, key2:=.Range("B2"), order2:=1
  nlr = Application.Ceiling(lr, 50)
  nc = 4
  For r = 51 To nlr Step 50
    .Range("A" & r & ":B" & r + 49).Copy .Cells(1, nc)
    Application.CutCopyMode = False
    nc = nc + 3
  Next r
  .Range("A51:B" & nlr).ClearContents
  .Columns.AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorgDataV2 macro.

Looks Awsome aswell tho it do not resize the empty rows in my excel (C,F,I,L,O,R and so on to AAU)

but i added this i found from nother thred

For i = Range("C1").Column To Range("AAX1").Column Step 3     Columns(i).ColumnWidth = 1
Next i

Witch seems to work

 

[ZaXaZ]

END RESULT...
The to all for the big help!

Here is the end result.
2 Awsome Macros
By Comfy and Hiker95 Both in link.
https://www.dropbox.com/sh/5d6473ylw85hmym/AABNsIUu-5V9YiTHQRbbc0zAa?dl=0

 

[hiker95]

ZaXaZ,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.