Need help fast w/ vba!

[nrguerrieri]

Need a macro that will find every cell that contains text within columns A to E all the way down to the bottom. Then paste them in that exact order, but all in column A.

 

[Rick Rothstein]

Need a macro that will find every cell that contains text within columns A to E all the way down to the bottom. Then paste them in that exact order, but all in column A.

Two questions...

Do you have some cells with numbers in them? If so, they are to be ignored, correct?

What do you mean by "exact order"? You have 5 columns and numerous rows... data can be ordered by traveling across a single row before moving down to the next row or down a single column before moving on to the next column... which ordering did you want?

 

[nrguerrieri]

its like this here are the columns

A. B. C. D. E
x
x
x

x

*after macro want it to look like

A. B. c. d. e
x
x
x

x

 

[nrguerrieri]

<img id="<9B9562F7-FF91-4A9E-9F8D-F5A3C20B2531@resolutecomm>" src="blob:https://www.mrexcel.com/6340cb64-92bf-4fb1-911e-dca532ae4a13" alt="IMG_8818.JPG" type="application/x-apple-msg-attachment" class="Apple-web-attachment Singleton" style="caret-color: rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: -webkit-standard;">

 

[My Aswer Is This]

I assume when you say Text you mean any value.

This script will use Column(40) as a helper column.
I assume you have nothing in column (40)

Sub Search_Range()
'Modified 5/10/2018 6:15 PM  EDT
Application.ScreenUpdating = False
Dim c As Range
Dim Lastrow As Long
Dim i As Long
i = 1
Dim b As Long
    For b = 1 To 5
        Lastrow = Cells(Rows.Count, b).End(xlUp).Row
            For Each c In Cells(1, b).Resize(Lastrow)
                If c.Value <> "" Then c.Copy Cells(i, 40): i = i + 1
            Next
    Next
Columns(40).Copy Columns(1)
Columns(40).ClearContents
Application.ScreenUpdating = True
End Sub

 

[nrguerrieri]

i need this do it it from column a to D. but all the way down to like row #50 ,000

 

[Rick Rothstein]

its like this here are the columns

A. B. C. D. E
x
x
x

x

*after macro want it to look like

A. B. c. d. e
x
x
x

x

Is there only one text value per row (as shown)? If so, and if there is no other data in Columns F onward, then you this single line of code (executed by itself or along with other code in a macro) will do what you want...

Columns("A:E").SpecialCells(xlBlanks).Delete xlShiftToLeft

 

[My Aswer Is This]

You said in Post 1:

Need a macro that will find every cell that contains text within columns A to E

My script works on columns A to E

​

 

[nrguerrieri]

youre right sorry. i mean A to E. will try right now

 

[nrguerrieri]

whoa that did not work. not sure what happened there. I need it to look from this to that.


before
<img id="<05ECE52D-D3B6-48FF-89F8-AAF12198EC21@resolutecomm>" src="blob:https://www.mrexcel.com/83ffa292-6c2b-4333-950b-3f72f8a39a04" alt="IMG_8819.JPG" type="application/x-apple-msg-attachment" class="Apple-web-attachment Singleton" style="caret-color: rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px;">




after
<img id="<F3FE943D-1A56-40F9-8150-3B775BA65377@resolutecomm>" src="blob:https://www.mrexcel.com/8eb80e06-4ea6-447d-b5ea-06d3019fa8f4" alt="IMG_8820.JPG" type="application/x-apple-msg-attachment" class="Apple-web-attachment Singleton" style="caret-color: rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px;">