Excel 2007 Need to copy column B to new row in Column A

[Dannottheman]

Hello everyone,

I need help. I have several 2007 worksheets with two column of data and many rows. I want to take the information in each row of column B and add it as a new row under column A. Is this possible with a VBA? If so, can someone help? Example (current worksheet):
Column A-------------- Column B
What's today?--------- Tuesday
What is this? ---------- It's an apple
What's your name?----My name is Mike

Expected result
Column A
What's today?
Tuesday
What is this?
It's an apple
What's your name?
My name is Mike

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub Dannottheman()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long
   
   Ary = Range("A2:B" & Range("A" & Rows.count).End(xlUp).Row).Value2
   ReDim Nary(1 To UBound(Ary) * 2, 1 To 1)
   For r = 1 To UBound(Ary)
      Nary(r * 2 - 1, 1) = Ary(r, 1)
      Nary(r * 2, 1) = Ary(r, 2)
   Next r
   Range("D2").Resize(UBound(Nary)).Value = Nary
End Sub

 

[Dannottheman]

Hi & welcome to MrExcel.
How about

Sub Dannottheman()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long
  
   Ary = Range("A2:B" & Range("A" & Rows.count).End(xlUp).Row).Value2
   ReDim Nary(1 To UBound(Ary) * 2, 1 To 1)
   For r = 1 To UBound(Ary)
      Nary(r * 2 - 1, 1) = Ary(r, 1)
      Nary(r * 2, 1) = Ary(r, 2)
   Next r
   Range("D2").Resize(UBound(Nary)).Value = Nary
End Sub

Genius! The only issue I detected is that it's taking data like 8/11 (fraction) and copying it as 11-Aug
Any mods that can be made to the VBA so the data is not changed? Thanks a lot in advance! I spent quite a bit of time searching online for something like this

 

[Fluff]

Maybe

Sub Dannottheman()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long
   
   Ary = Range("A2:B" & Range("A" & Rows.count).End(xlUp).Row).Value2
   ReDim Nary(1 To UBound(Ary) * 2, 1 To 1)
   For r = 1 To UBound(Ary)
      Nary(r * 2 - 1, 1) = Ary(r, 1)
      Nary(r * 2, 1) = Ary(r, 2)
   Next r
   With Range("D2").Resize(UBound(Nary))
      .NumberFormat = "@"
      .Value = Nary
   End With
End Sub

 

[Dannottheman]

Maybe

Sub Dannottheman()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long
  
   Ary = Range("A2:B" & Range("A" & Rows.count).End(xlUp).Row).Value2
   ReDim Nary(1 To UBound(Ary) * 2, 1 To 1)
   For r = 1 To UBound(Ary)
      Nary(r * 2 - 1, 1) = Ary(r, 1)
      Nary(r * 2, 1) = Ary(r, 2)
   Next r
   With Range("D2").Resize(UBound(Nary))
      .NumberFormat = "@"
      .Value = Nary
   End With
End Sub

That did it! True genius. Do you know why certain rows are showing the data as #####################? However, when I click on the cell I can see on the bar below the tools that the text is correct but in the excel row it appears as ########################. This is happening for a handful of rows. I need to print this as a PDF and it is printing the data as ##################### instead of the actual text.

 

[Fluff]

What should the value of those cells be?

 

[Dannottheman]

What should the value of those cells be?

In one case, this: Triangle UVW has vertices U(6 9) V(6 0) and W(3 3) and will be dilated by a scale factor of 2/3 with the center of dilation at the origin. Then triangle U´V´W´will be translated 4 units left and 6 units down. What are the coordinates for triangle U´´V´´W´´ (after dilation and translation)?

But again, it is showing it correctly in the bar above.

 

[Dannottheman]

Just found the solution (I think), I need to set the row as "general" under format. Thanks a lot for your help. Thank you. Also, would you happen to know of a VBA that will "shade/color" every two rows? As you can see, these are question/answer, question/answer, etc. I want to shade/color every two rows (rows 4,5 (same color), 6,7 (same color), 8,9 (same color), 10, 11 (same color). In other words, I know I can shade "every other row" in Excel but I don't want to do this. I want to shade in pairs of two.

 

[Fluff]

Glad you sorted it & thanks for the feedback.

You will need to start a new thread for your new question. Thanks