troubleshooting Question

issues transposing data to multiple cells

Avatar of Joe Reichsfeld
Joe ReichsfeldFlag for United States of America asked on
* multipleVBA
4 Comments1 Solution99 ViewsLast Modified:
I deal with massive amounts of data on a regular basis.  With assistance here, I've been able to systematically simplify dealing with what could be an overwhelming nightmare, thanks.

I have a column of cells filled with multiple URLs per cell.  I need to transpose them from one cell to multiple successive cells in the same row. Once it is transposed, I need to eliminate all blank cells shifting everything left and finally, any cell that begins with the word large and follows a cell that begins with the word hires should be deleted and everything shifted left.  Two successive cells beginning with large but following a hires cell would only eliminate the first large cell. A large must follow a hires to be eligible for deletion

Seems easy but has been nothing of the sort.  I can't get past the transpose.  I've tried to add line breaks, find and replace, multiple different things but everything I do results in losing most of the data past the first entry

I've included an example with a macro I recorded.  It copies the cell contents, goes to a blank worksheet, pastes them vertically, copies the results and transposes them back onto the original worksheet.  Unfortunately, it doesn't work as it is too specific as to the contents of the cell

Cell Contents might look like this :
hiRes":"https://images-na.ssl-images-amazon.com/images/I/619YB71mfL._UL1500_.jpg
large":"https://images-na.ssl-images-amazon.com/images/I/41jexIwZeL.jpg
hiRes":"https://images-na.ssl-images-amazon.com/images/I/61EodGNNjL._UL1500_.jpg
large":"https://images-na.ssl-images-amazon.com/images/I/41ib6yY1wL.jpg
hiRes":"https://images-na.ssl-images-amazon.com/images/I/61bkOe2JYL._UL1500_.jpg
large":"https://images-na.ssl-images-amazon.com/images/I/51NDH6hruL.jpg
large":"https://images-na.ssl-images-amazon.com/images/I/51tsIdIZbL.jpg
hiRes":"https://images-na.ssl-images-amazon.com/images/I/61mCsRqgoL._UL1000_.jpg


macro:
'breakout multiple URLs from one cell with many URLs to multiple cells in a row with one URL each
    ActiveCell.FormulaR1C1 = _
        "hiRes"":""https://images-na.ssl-images-amazon.com/images....removed to conserve space but this was to specific
    Range("E1").Select
    Sheets("Sheet2").Select
    ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
        DisplayAsIcon:=False, NoHTMLFormatting:=True
    Selection.Copy
    Sheets("Sheet1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    ActiveWindow.SmallScroll Down:=1
    Range("D2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "hiRes"":""https://images-na.ssl-images-amazon.com/images/I....removed to conserve space but this was to specific
    Sheets("Sheet2").Select
    Range("A1").Select
    ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
        DisplayAsIcon:=False, NoHTMLFormatting:=True
    Selection.Copy
    Sheets("Sheet1").Select
    Range("E2").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    ActiveWindow.SmallScroll Down:=1
    Range("D3").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "hiRes"":""https://images-na.ssl-images-amazon.com/images/I/6....removed to conserve space but this was to specific
    Sheets("Sheet2").Select
    Range("A1").Select
    ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
        DisplayAsIcon:=False, NoHTMLFormatting:=True
    Selection.Copy
    Sheets("Sheet1").Select
    Range("E3").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    ActiveWindow.SmallScroll Down:=1

Open in new window


The first 3 or four lines of the sample file appear as they should after the actual transpose occurs

Any assistance is always appreciated.
imgBreakout.xlsm
ASKER CERTIFIED SOLUTION
NorieSenior Associate
Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 4 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 4 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004