Home

How to interleave column data into rows

%3CLINGO-SUB%20id%3D%22lingo-sub-309075%22%20slang%3D%22en-US%22%3EHow%20to%20interleave%20column%20data%20into%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-309075%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20need%20some%20help%20with%20a%20piece%20of%20code%2C%20or%20I%20need%20a%20good%20idea.%20I%20have%20a%20spreadsheet%20with%20two%20columns.%20Column%20A%20contains%20a%20series%20of%20author%20names%2C%20and%20B%20contains%20the%20titles%20to%20their%20books%20or%20articles.%20I%20have%20a%20piece%20of%20code%20that%20interweaves%20these%20columns%20into%20rows%2C%20as%20shown%20below.%20However%2C%20when%20I%20make%20a%20text%20file%20out%20of%20it%20to%20import%20to%20EndNote%2C%20I%20need%20a%20space%20between%20each%20author%2Ftitle%20set.%20Ideally%20the%20code%20I%20have%20would%20be%20modified%20to%20add%20a%20space%20in%20the%20Excel%20file%2C%20so%20it%20would%20look%20like%20the%20example%20below.%3C%2FP%3E%3CP%3EI%20would%20greatly%20appreciate%20any%20help%20you%20folks%20can%20provide.%20Thank%20you.%3C%2FP%3E%3CP%3EHere%20is%20the%20code%20I%20referenced%3A%3C%2FP%3E%3CP%3ESub%20Two_To_One()%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20False%3CBR%20%2F%3EDim%20numRows%20As%20Integer%3CBR%20%2F%3EDim%20R%20As%20Long%3CBR%20%2F%3EnumRows%20%3D%201%3CBR%20%2F%3EFor%20R%20%3D%202000%20To%201%20Step%20-1%3CBR%20%2F%3EActiveSheet.Rows(R%20%2B%201).Resize(numRows).EntireRow.Insert%3CBR%20%2F%3ENext%20R%3CBR%20%2F%3ERange(%22B1%22).Select%3CBR%20%2F%3ESelection.Insert%20Shift%3A%3DxlDown%3CBR%20%2F%3EColumns(%22A%3AA%22).Select%3CBR%20%2F%3ESelection.SpecialCells(xlCellTypeBlanks).Select%3CBR%20%2F%3ESelection.Delete%20Shift%3A%3DxlToLeft%3CBR%20%2F%3ERange(%22A1%22).Select%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20True%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3CP%3E%25A%20Bill%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%25T%20The%20Big%20Tree%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E%25A%20Joe%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%25T%20The%20Little%20Rock%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E%25A%20Bill%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E%25T%20The%20Big%20Tree%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E%25A%20Joe%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E%25T%20The%20Little%20Rock%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-309075%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
michaedo1
Occasional Visitor

Hello,

I need some help with a piece of code, or I need a good idea. I have a spreadsheet with two columns. Column A contains a series of author names, and B contains the titles to their books or articles. I have a piece of code that interweaves these columns into rows, as shown below. However, when I make a text file out of it to import to EndNote, I need a space between each author/title set. Ideally the code I have would be modified to add a space in the Excel file, so it would look like the example below.

I would greatly appreciate any help you folks can provide. Thank you.

Here is the code I referenced:

Sub Two_To_One()
Application.ScreenUpdating = False
Dim numRows As Integer
Dim R As Long
numRows = 1
For R = 2000 To 1 Step -1
ActiveSheet.Rows(R + 1).Resize(numRows).EntireRow.Insert
Next R
Range("B1").Select
Selection.Insert Shift:=xlDown
Columns("A:A").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Application.ScreenUpdating = True
End Sub

%A Bill

%T The Big Tree

%A Joe

%T The Little Rock

  
  

%A Bill

 

%T The Big Tree

 
  

%A Joe

 

%T The Little Rock

 
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies