Range Select/Transpose Macro

Christopher Hanna

New Member
Joined
Dec 20, 2013
Messages
11
I want to make a macro/VBA script that will search a single column for separators (dashes), select the rows between the dashes and transpose that data into another worksheet. There are several hundred "records" laid out this way.

Ideally, I'd like the macro to find/select the next range and keep going to the bottom of the column. Possible?
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
Christopher Hanna,

Welcome to the MrExcel forum.


What version of Excel and Windows are you using?

Can you post a screenshot of the actual raw data worksheet, and, post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker
Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
See reply #2 the BLUE text in the following link:
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.
 

Christopher Hanna

New Member
Joined
Dec 20, 2013
Messages
11
At present, I only have test/sample data, but it's this times 400+ instances, so looking to take some of the manual copy/transpose+switch worksheets out of the process. Thanks.



---------------
Smith, John C.
President
Asst: Jim Jones
Phone: 123-456-7890
Fax: 123-456-7890
Email: jsmith@bizco.com
Biziness Company
One Tycoon Drive
Bigstown, ST 10101
Tag 1
Tag 2
Equities/Securities
Notes
---------------
Smith, Larry
President
Phone: 123-456-7890
Fax: 123-456-7890
Email: jsmith@bizco.com
Biziness Company
One Tycoon Drive
Bigstown, ST 10101
Tag 1
Equities/Securities
Notes
---------------
Jones, Fred
President
Phone: 123-456-7890
Fax: 123-456-7890
Email: jsmith@bizco.com
Biziness Company
One Tycoon Drive
Bigstown, ST 10101
Tag 1
Equities/Securities
Notes
---------------
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,800
Office Version
2010
Platform
Windows
Give this macro a try...

Code:
Sub TransposeBetweenDashes()
  Dim Max As Long, Ar As Range, C As Range
  Columns("A").Replace "---------------", "", xlPart
  Set C = Columns("A").SpecialCells(xlConstants)
  If Not C Is Nothing Then
    For Each Ar In C.Areas
      Ar(1).Resize(, Ar.Count) = Application.Transpose(Ar)
      If Ar.Count > Max Then Max = Ar.Count
    Next
    Range("A1").Formula = "Header 1"
    Range("A1").AutoFill Destination:=Range("A1").Resize(, Max), Type:=xlFillDefault
    Range("B2:B" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlBlanks).EntireRow.Delete
  End If
End Sub
 

Christopher Hanna

New Member
Joined
Dec 20, 2013
Messages
11
Holy quick response and massive data transposition! Give this man the Iron Man brilliance award. :) Thank you!

Any way to put the transposed data in successive rows?

 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,800
Office Version
2010
Platform
Windows
Any way to put the transposed data in successive rows?
Did you mean "successive columns" (that is, line up like item under each other in their own row)? I don't think so. It would be so much easier to do if each line of data started with its own keyword followed by a colon (like "Phone:", "Email:", etc.), but some of your data does not have keywords and they might not be in each group of data (such as Tag1 and Tag2). Also, I do not know which non-keyword lines of data could possibly not appear (for instances, the business address before the Tag#)... with this possible variation, it is hard to have a program decide which non-keyworded lines of data belong to which category.
 

Christopher Hanna

New Member
Joined
Dec 20, 2013
Messages
11
Per my screenshot, I did mean successive rows. I know the data won't match any header layout, and that's OK for this step. As you've pointed out, the data doesn't have consistent keywords to do this. So I'll have to do more massaging.

I'll use arbitrary references here. I'm looking to transpose the data between dashes in column A1 to rows starting at B1.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,800
Office Version
2010
Platform
Windows
Per my screenshot, I did mean successive rows. I know the data won't match any header layout, and that's OK for this step. As you've pointed out, the data doesn't have consistent keywords to do this. So I'll have to do more massaging.

I'll use arbitrary references here. I'm looking to transpose the data between dashes in column A1 to rows starting at B1.
So my code did not do what you wanted? Let me see if I understand what you need then. You want the list my code produced to start in Column B and you want the original data to remain untouched... is that correct? If so...

Code:
Sub TransposeBetweenDashes()
  Dim Max As Long, Ar As Range, C As Range
  Columns("A").Replace "---------------", "", xlPart
  Set C = Columns("A").SpecialCells(xlConstants)
  If Not C Is Nothing Then
    For Each Ar In C.Areas
      Ar(1).Offset(, 1).Resize(, Ar.Count) = Application.Transpose(Ar)
      If Ar.Count > Max Then Max = Ar.Count
    Next
    Range("B1").Formula = "Header 1"
    Range("B1").AutoFill Destination:=Range("B1").Resize(, Max), Type:=xlFillDefault
    Intersect(Range("B1").Resize(, Max).EntireColumn, Range("B2:B" & Cells(Rows.Count, _
                "A").End(xlUp).Row).SpecialCells(xlBlanks).EntireRow).Delete xlShiftUp
  End If
End Sub
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,800
Office Version
2010
Platform
Windows
So my code did not do what you wanted? Let me see if I understand what you need then. You want the list my code produced to start in Column B and you want the original data to remain untouched... is that correct? If so...

Code:
Sub TransposeBetweenDashes()
  Dim Max As Long, Ar As Range, C As Range
  Columns("A").Replace "---------------", "", xlPart
  Set C = Columns("A").SpecialCells(xlConstants)
  If Not C Is Nothing Then
    For Each Ar In C.Areas
      Ar(1).Offset(, 1).Resize(, Ar.Count) = Application.Transpose(Ar)
      If Ar.Count > Max Then Max = Ar.Count
    Next
    Range("B1").Formula = "Header 1"
    Range("B1").AutoFill Destination:=Range("B1").Resize(, Max), Type:=xlFillDefault
    Intersect(Range("B1").Resize(, Max).EntireColumn, Range("B2:B" & Cells(Rows.Count, _
                "A").End(xlUp).Row).SpecialCells(xlBlanks).EntireRow).Delete xlShiftUp
  End If
End Sub
Sorry, I forgot to put the 15 dashes back in between the data groups; use this code instead of the above code...

Code:
Sub TransposeBetweenDashes()
  Dim Max As Long, Ar As Range, C As Range
  Columns("A").Replace "---------------", "", xlPart
  Set C = Columns("A").SpecialCells(xlConstants)
  If Not C Is Nothing Then
    For Each Ar In C.Areas
      Ar(1).Offset(, 1).Resize(, Ar.Count) = Application.Transpose(Ar)
      If Ar.Count > Max Then Max = Ar.Count
    Next
    Range("B1").Formula = "Header 1"
    Range("B1").AutoFill Destination:=Range("B1").Resize(, Max), Type:=xlFillDefault
    Intersect(Range("B1").Resize(, Max).EntireColumn, Range("B2:B" & Cells(Rows.Count, _
                "A").End(xlUp).Row).SpecialCells(xlBlanks).EntireRow).Delete xlShiftUp
    Range("A1:A" & C.Count).SpecialCells(xlBlanks).Value = String(15, "-")
  End If
End Sub
 

Christopher Hanna

New Member
Joined
Dec 20, 2013
Messages
11
Not at all, sir. Your code worked admirably and brought to mind something I hadn't thought of prior: using the dashes to put records on separate rows! The revised code is doing is adding dashes in column A. Previously you mentioned lining up the data (impossible, no consistent header), but lining up the dashes (start of record) would be grand.

 

Watch MrExcel Video

Forum statistics

Threads
1,095,172
Messages
5,442,821
Members
405,198
Latest member
Florence Thomas

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top