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?
 
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.

Data%20Output%203.png
The only reason I put the dash back into Column A is because my code removed them in order to do its work... since they were part of your original data, I felt I should put them back (since you wanted to keep all the original data in Column A. As for your latest request...
I am not exactly sure what you are asking for. Do you want Column B to be nothing but dashes? Or are you asking to have each transposed row of data aligned with the dash (above) the data section that was transposed? Or were you asking for something completely different?
 
Upvote 0

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Apologies for the confusion here. I'll try my best to describe and illustrate.

For several hundred rows, I want to transpose the information between the dashes (doesn't matter if the dashes are kept or not) in column A into rows (same worksheet or another isn't important to me). The amount of information between the dashes is not consistent in length.

I know I'll have to align the columns manually later. I'm trying to transpose a large set of vertical data into rows using the dashes as an indicator of "next row".

Data%20Output%204.png
 
Upvote 0
Apologies for the confusion here. I'll try my best to describe and illustrate.

For several hundred rows, I want to transpose the information between the dashes (doesn't matter if the dashes are kept or not) in column A into rows (same worksheet or another isn't important to me). The amount of information between the dashes is not consistent in length.

I know I'll have to align the columns manually later. I'm trying to transpose a large set of vertical data into rows using the dashes as an indicator of "next row".

Data%20Output%204.png
But... but.. that is what the first code I posted (in Message #4) did... and you indicated you wanted something else. Here is the code again (I have changed nothing)... is it not producing the list you say you wanted for you?

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
 
Upvote 0
Ah…ok. I see where I've misunderstood your inquires, sir. Running that code puts all the records into one row. When I use more of the test data (and spreadsheet width is maxed), then the output wraps onto a new row. You can see the next record occurs at column O (marked "Header 15") instead of the next row (A2).

My regrets for my misunderstanding, but you've nevertheless given me excellent ideas of how else to use this code.

Data%20Output%205.png
 
Upvote 0
Ah…ok. I see where I've misunderstood your inquires, sir. Running that code puts all the records into one row. When I use more of the test data (and spreadsheet width is maxed), then the output wraps onto a new row. You can see the next record occurs at column O (marked "Header 15") instead of the next row (A2).
Ah, I think I know what might be happening. See if this macro gives you the output you want...

Code:
Sub TransposeBetweenDashes()
  Dim Max As Long, Addr As String, Ar As Range, C As Range
  Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
  Range(Addr) = Evaluate("IF(MID(" & Addr & ",2,3)=""---"",""""," & Addr & ")")
  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
 
Upvote 0
Shazam! That's it. I can't tell you how many times I've banged my head on this.

The reason why I chose to join Mr.Excel was the sheer number of hits on Google. This being my first experience, I'm absolutely stunned at the depth and speed of response. I'm blown away! You're officially my new superhero. I have so much more to learn! Thank you for your tireless support and patience.
 
Upvote 0
Shazam! That's it. I can't tell you how many times I've banged my head on this.

The reason why I chose to join Mr.Excel was the sheer number of hits on Google. This being my first experience, I'm absolutely stunned at the depth and speed of response. I'm blown away! You're officially my new superhero. I have so much more to learn! Thank you for your tireless support and patience.
You are quite welcome. I am glad I was able to have been of some help to you... and please come back to this forum for any other questions you might have.
 
Upvote 0
In continuing to refine my data, I ran across another one of your gems:

=COUNTIF($D$1:$D1,"SearchWord*")

Helped tremendously to find strings without returning a #VALUE! Thanks again for all your contributions and wisdom, sir.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top