simple select two columns to last row and transpose to next cell

Dave01

Board Regular
Joined
Nov 30, 2018
Messages
99
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi,

this seems pretty simple but for some reason I cant get it to work as copy two rows of data to last row and pasting is easy enough, but this time I need to transpose an keep getting an error saying the paste area isnt the same.

Ideally there has to be up to last row, because the length of the data will change, so I cant just do range 1 to 1000 etc.

its simple enough, select data in A1: B & last row, copy and transpose to C1


close code is

Dim LC As Long
LC = Cells(1, Columns.Count).End(xlToLeft).Column
Columns(LC).Copy
Cells(1, LC + 1).PasteSpecial Paste:=xlPasteValues, SkipBlanks:=True, Transpose:=True


And

Last_Row = ActiveSheet.Cells(Rows.Count, "b").End(xlUp).Row
Range("A1:B" & Last_Row).End(xlUp).Copy
Range("C1:C" & Last_Row).PasteSpecial Paste:=xlValues, Transpose:=True



but either it doesnt transpose, or causes an error paste value not the same.


Grateful to anyone who can unjam me .

Many thanks

David.

Link to spreadsheet

 

Attachments

  • 1.JPG
    1.JPG
    43.9 KB · Views: 6

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Sequoyah

Board Regular
Joined
Mar 26, 2017
Messages
66
Hi David,
try this code:
VBA Code:
Sub test20210521()

Dim DirArray As Variant
Dim Destination As Range
    Set Destination = Sheets(1).Range("C1")

    DirArray = Sheets(1).Range("A1:B" & Range("A" & Rows.Count).End(xlUp).Row).Value

    Destination.Resize(UBound(DirArray, 2), UBound(DirArray, 1)).Value = Application.Transpose(DirArray)

End Sub
 

Dave01

Board Regular
Joined
Nov 30, 2018
Messages
99
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi David,
try this code:
VBA Code:
Sub test20210521()

Dim DirArray As Variant
Dim Destination As Range
    Set Destination = Sheets(1).Range("C1")

    DirArray = Sheets(1).Range("A1:B" & Range("A" & Rows.Count).End(xlUp).Row).Value

    Destination.Resize(UBound(DirArray, 2), UBound(DirArray, 1)).Value = Application.Transpose(DirArray)

End Sub
Hi Sequoyah,

Perfect, thank you, I knew it was something along those those lines, I just couldnt get the count correct.​


the results where exactly as I wanted, I do have another question if you dont mind, and it would save me time if you could help, as to complete the spreadsheet I need to fill right based on the number of cells from C1 to the end of the last data in the columns, in this case its column CC.

I have got a program somewhere where where I have done this before, but its a case of going through it and stripping it out.

I imagine it is a case like Column count C1 to Col.count
then fill right.

I know its cheeky, but thanks for resolving my original issue.



Dave.
 

Sequoyah

Board Regular
Joined
Mar 26, 2017
Messages
66
Hi Dave01,
thanks for the feedback. I can't open your workbook as it contains links to external sources. Maybe the following code can do the trick, otherwise it is better to open a new thread:
VBA Code:
Sub test()

Dim lastcolumn As Long

lastcolumn = Cells(2, Columns.Count).End(xlToLeft).Column

Range("C3").AutoFill Destination:=Range(Cells(3, 3), Cells(3, lastcolumn))

End Sub
 
Solution

Dave01

Board Regular
Joined
Nov 30, 2018
Messages
99
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Perfect, Thank you,

I changed the code slightly so it fills accross and and all down to populate every cell that needs to be cell, but withought your help I would have still been scratching my help.


Sheets("Payment Forecast").Activate
Range("C3").Activate
Dim lastcolumn, lastRow As Long
ActiveCell.FormulaR1C1 = "=SUMIFS('Raw Data'!C8,'Raw Data'!C14,RC1,'Raw Data'!C9,RC2,'Raw Data'!C6,'Payment Forecast'!R1C)"
lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Range("c3").AutoFill Destination:=Range("C3:C" & lastRow)
lastcolumn = Cells(2, Columns.Count).End(xlToLeft).Column
Range("C3:C" & lastRow).AutoFill Range("C3", Cells(lastRow, lastcolumn))
 

Forum statistics

Threads
1,148,249
Messages
5,745,619
Members
423,963
Latest member
Rayds

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top