New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

using VBA to sort data

Macros, VBA, Excel Automation, etc.

using VBA to sort data

Postby ricksimpson » Tue Mar 08, 2016 4:49 pm

I have a file I need to import into a spreadsheet to feed other pages. The data is in one column and needs to go into a row. Have that part figured out, but the data has a couple different items, such as this is partial list
va221
va223
hf2301
hf2301 s
hf2301 m
hf2301 l What I would like to have is the 1,2,3 items in top row and the 4,5,6 under HF2301 having trouble getting the information under the top row. Any help would greatly appreciated.

Thanks

Ricksimpson
  • 0

ricksimpson
Rookie
 
Posts: 1
Joined: Mar 8, 2016
Reputation: 0
Excel Version: 2010

Re: using VBA to sort data

Postby NoSparks » Fri Mar 11, 2016 1:53 pm

I hate to see you not get any assistance, but your explanation of what's wanted needs a little expansion.

My gut feeling is that it's not sorting you're after, as your example data is not sorted in any manner and you don't specify any particular order to use.

I'm thinking you want the original single column transposed into multiple rows.

Assuming it's always 3 items per row, and the item order as shown, and your data starts in A2, maybe something along the lines of this...
Code: Select all
Sub Testing()

    Dim i As Integer
    Dim lr As Long
   
  With Sheets("Sheet1")
    lr = .Cells(Rows.Count, "A").End(xlUp).Row
    For i = 2 To lr Step 3
      .Cells(i, 1).Offset(1).Resize(2, 1).Copy
      .Cells(i, 2).PasteSpecial Paste:=xlPasteValues, Transpose:=True
    Next i
    'remove lines with col B blank
    .Range("B2:B" & lr).SpecialCells(xlBlanks).EntireRow.Delete
    'reposition cursor
    .Range("A1").Select
    'stop the marching ants
    Application.CutCopyMode = False
  End With

End Sub
  • 0

NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010


Return to Macros and VBA Questions

Who is online

Users browsing this forum: Google [Bot] and 65 guests