0

EXCEL: If I want to copy the data from cells B2 to B22 and then paste special/transpose the data to cell A27 I can simply use the below script. However, if A27 already has data in it, I need the script to look for the next blank row below 27 and put the data there. How do I change my script to be able to do this please?

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("B1:B22").Select
    Selection.Copy
    ActiveWindow.SmallScroll Down:=6
    Range("A27").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
End Sub
BigBen
  • 46,229
  • 7
  • 24
  • 40
rnmuk
  • 53
  • 5
  • Hi @BigBen, the idea is that if cell A27 is blank the result can be pasted there, however, if A27 already has data in it, it will then check A28 continuing down until it finds an empty cell, where it can then paste the data. – rnmuk Aug 19 '21 at 19:51
  • `ActiveSheet.Range("A100000").End(xlUp).Offset(1,0).Resize(,23).Value = Application.Transpose(ActiveSheet.Range("B1:B22").value)` – Scott Craner Aug 19 '21 at 21:07

0 Answers0