Copy data from multiple columns into a single column

Question

I have 3 columns A, B, C and I want to make a column D with values in A, B, C but it should include ">=", "<=" signs as well. The script I am working on does help me loop around columns and copy its data to a new column. Can anyone help me figure out how I can add those special characters at the beginning of the numbers in the cells? Thanks for any help.

Sub Try()

With ActiveWorkbook.Sheets("Sheet1")
    For rw = 1 To .Rows.Count
        If (.Rows(rw).Columns("A:A").Value <> "") Then
            .Rows(rw).Columns("A:A").Copy .Range("D" & rw)
        End If
    Next rw
    .Columns("A:A").Delete

End With

End Sub

So the >= and <= are not in the data currently? You want to go through the pasted data, and add >= or <= to that data? How do you know what cell gets what sign? I would just loop through each cell in the copied range, adding the symbols, no? — BruceWayne
– BruceWayne, Commented Apr 13, 2018 at 14:18
@BruceWayne: from what I can gather from the example sheet, if text is in A, we add >=, if its column B its <= and if its column C, no sign is added. @PowerToYou, you can achieve this with formulas, is there a reason why you want to achieve this with VBA? — Zac
– Zac, Commented Apr 13, 2018 at 14:20
So, the symboles would depend on what columns the data would come from. For example, Column A could have ">=" and B could have "<=". The issue is I have about 12 columns and I don't know what would be the most efficient way to do this. @BruceWayne — PowerToYou
– PowerToYou, Commented Apr 13, 2018 at 14:21
Yes, I have already developed a macro which does other tasks. I only want this as an addition to the script I have already developed.@BruceWayne — PowerToYou
– PowerToYou, Commented Apr 13, 2018 at 14:22

Gary's Student · Accepted Answer · 2018-04-13 14:47:06Z

3

With data in cols A through C, in D1 enter:

=IF(A1<>"",">="&A1,IF(B1<>"","<="&B1,C1))

and copy down:

EDIT#1:

To do this with VBA:

Sub PopulateFormulas()
    Dim N As Long, s As String

    s = "=IF(A1<>"""","">=""&A1,IF(B1<>"""",""<=""&B1,C1))"
    N = Range("A1").CurrentRegion.Rows.Count
    Range("D1:D" & N).Formula = s
End Sub

edited Apr 13, 2018 at 14:47

answered Apr 13, 2018 at 14:32

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zac Over a year ago

My thought exactly. Don't need VBA for this.. easily achieved with formula

dwirony Over a year ago

Plus one for simplicity :). If OP can use formulas then he should use this.

dwirony · Accepted Answer · 2018-04-13 14:42:55Z

1

Probably not the most elegant solution (and you probably don't even need VBA for this, a formula would most likely suffice), but this does the trick:

Sub Test()

arr = Array(">=", "<=", "")

With ActiveWorkbook.Sheets("Sheet1")
    For cl = 1 To 3
        For rw = 2 To .Cells(ActiveSheet.Rows.Count, "C").End(xlUp).Row
            If .Cells(rw, cl).Value <> "" Then
                .Cells(rw, 4).Value = arr(cl - 1) & .Cells(rw, cl).Value
            End If
        Next rw
    Next cl
End With

'If you still need to delete those columns at the end-
'ActiveWorkbook.Sheets("Sheet1").Columns("A:C").Delete xlShiftLeft
End Sub

edited Apr 13, 2018 at 14:42

answered Apr 13, 2018 at 14:23

dwirony

5,4604 gold badges26 silver badges49 bronze badges

Comments

score 0 · Accepted Answer · 2018-04-13 17:44:53Z

0

This worked for me:

Sub macro_test()


k = 1

For k = 1 To 3
t = 2
lr = ActiveSheet.Cells(100000, k).End(xlUp).Row

Do Until t > lr

If Cells(t, k).Value = “” Then GoTo  continue

If k = 1 Then Cells(t, 4).Value = ">=" & Cells(t, k).Value
If k = 2 Then Cells(t, 4).Value = "<=" & Cells(t, k).Value
If k = 3 Then Cells(t, 4).Value = "" & Cells(t, k).Value
continue:
t = t + 1

Loop

Next

End Sub

edited Apr 13, 2018 at 17:44

answered Apr 13, 2018 at 14:45

user8608712

6 Comments

PowerToYou Over a year ago

Thank you so much for the help guys. One last question: What if I encounter an empty cell/cells? For example, say Column C is empty sometimes. How can I still make it work for Columns A and B?

user8608712 Over a year ago

you can add a "If lr=1 Then Resume Next" (the code is calculating last row, so if it's =1 that means, it's empty - only has the title on it)

PowerToYou Over a year ago

Thanks a lot for the help @IRENE G this script only works for printing out the data from column A and still prints out ">=" for all other cells that are blank in column A, but are present in B or C

user8608712 Over a year ago

You may have blank cells in column A... I changed a little bit the code to skip these... hope it helps!

PowerToYou Over a year ago

I still cannot print values from cells in the 2nd and 3rd columns. It leaves them blank. Only values from column A get printed out. @IRENE G

|

DisplayName · Accepted Answer · 2018-04-13 17:53:30Z

0

try this

Sub main()
    Dim iCol As Long, cell As Range, signs As Variant

    signs = Array(">=", "<=", "")
    For iCol = 1 To 3
        For Each cell In Columns(iCol).SpecialCells(xlCellTypeConstants, xlNumbers)
            cell.Value = signs(iCol - 1) & cell.Value                
        Next
    Next
End Sub

if your columns A, B and C not empty cells content is not a numeric one only, then you could use:

For Each cell In Columns(iCol).SpecialCells(xlCellTypeConstants)

while if it's some formula, then you could use:

For Each cell In Columns(iCol).SpecialCells(xlCellTypeFormulas)

edited Apr 13, 2018 at 17:53

answered Apr 13, 2018 at 17:32

DisplayName

13.4k2 gold badges13 silver badges19 bronze badges

4 Comments

PowerToYou Over a year ago

Thanks a lot for helping, this throws an error on the For loop for cells part (i.e. the second for loop)

DisplayName Over a year ago

do you have "numbers" in your cells?

PowerToYou Over a year ago

Yes, I have all numbers in my columns. They are either numbers or empty @DisplayName

DisplayName Over a year ago

Then my code should work as it is. But you may try changing SpecialCells parameter as per those other two alternatives I showed

Collectives™ on Stack Overflow

Copy data from multiple columns into a single column

4 Answers 4

2 Comments

Comments

6 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

6 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related