How to Paste Special Transpose selected range by VBA until data end?

jjuliet115 · Jul 4, 2018

Hello Br..

Am new in VBA and doing repeated work in excel to make some format. I want to select some range and paste special as transpose.
Example : Copy Range A2:B5 and paste special to cell D1. Then delete row range A2:B5,
Then copy next row range A7:B9 and paste special transpose into next row in D6 column.....until end data in column A & B.

Please help me...for VBA Code

SMDB(TYPE 1)		Qty: 10 Nos	10W TPN	Ïncomer	Outgoings
10W TPN				200A TP MCCB, 36KA - 1 No	40A TP MCCB, 30KA - 6 Nos	63A TP MCCB, 30KA - 4 Nos
Ïncomer	200A TP MCCB, 36KA - 1 No
Outgoings	40A TP MCCB, 30KA - 6 Nos
	63A TP MCCB, 30KA - 4 Nos
SMDB(TYPE 2)		Qty: 15 Nos
10W TPN
Ïncomer	160A TP MCCB, 36KA - 1 No
Outgoings	63A TP MCCB, 30KA - 10 Nos
SMDB(TYPE 3)		Qty: 30 Nos

Fluff · Jul 4, 2018

Hi & welcome to the board
Is this what you want

Code:

Sub transposeData()
   Dim rng As Range
   
   For Each rng In Range("B:B").SpecialCells(xlConstants).Areas
      rng.Offset(-2, 2).Resize(2, rng.Count).Value = Application.Transpose(rng.Offset(, -1).Resize(, 2))
      rng.EntireRow.Delete
   Next rng
End Sub

jjuliet115 · Jul 5, 2018

Fluff said:

Hi & welcome to the board
Is this what you want

Code:

Sub transposeData()
   Dim rng As Range
   
   For Each rng In Range("B:B").SpecialCells(xlConstants).Areas
      rng.Offset(-2, 2).Resize(2, rng.Count).Value = Application.Transpose(rng.Offset(, -1).Resize(, 2))
      rng.EntireRow.Delete
   Next rng
End Sub

Thank you Fluff for your reply...

It's shows run time error 1004 in below line.

[ rng.Offset(-2, 2).Resize(2, rng.Count).Value = Application.transpose(rng.Offset(, -1).Resize(, 2)) ]

Could you please update me...

Fluff · Jul 5, 2018

Is your data as shown in the op, or do you have a header row?

jjuliet115 · Jul 5, 2018

Fluff said:
Is your data as shown in the op, or do you have a header row?

Yes brother..If you see my data table SMDB(TYPE 2) in column A6, data A7:B9 need to copy and transpose to D6, Then A7:B9 data need to delete.
this process want to continue until data.

We can say SMDB(TYPE 2) and Qty : 15 Nos as Header...

between headers data will be named as 10W TPN,Incomer and outgoings... This data need to place in header row..

so data will be in each header row...

Am looking output like this...

A B C D E F

SMDB(TYPE 1)	Qty : 10 Nos	10W TPN	200A TP MCCB, 36KA - 1 No	40A TP MCCB, 30KA - 6 Nos	63A TP MCCB, 30KA - 4 Nos
SMDB(TYPE 2)	Qty: 15 Nos	10W TPN	160A TP MCCB, 36KA - 1 No	63A TP MCCB, 30KA - 10 Nos

Thanks Bro...

Fluff · Jul 5, 2018

Does your original data look like this

Excel 2013/2016

A

B

C

1

SMDB(TYPE 1)

Qty: 10 Nos

2

10W TPN

3

ncomer

200A TP MCCB, 36KA - 1 No

4

Outgoings

40A TP MCCB, 30KA - 6 Nos

5

63A TP MCCB, 30KA - 4 Nos

6

SMDB(TYPE 2)

Qty: 15 Nos

7

10W TPN

8

ncomer

160A TP MCCB, 36KA - 1 No

9

Outgoings

63A TP MCCB, 30KA - 10 Nos

10

SMDB(TYPE 3)

Qty: 30 Nos

Sheet3

Or is there anything above it?

jjuliet115 · Jul 5, 2018

Fluff said:
Does your original data look like this

Excel 2013/2016

A B C
1 SMDB(TYPE 1) Qty: 10 Nos
2 10W TPN
3 Ïncomer 200A TP MCCB, 36KA - 1 No
4 Outgoings 40A TP MCCB, 30KA - 6 Nos
5 63A TP MCCB, 30KA - 4 Nos
6 SMDB(TYPE 2) Qty: 15 Nos
7 10W TPN
8 Ïncomer 160A TP MCCB, 36KA - 1 No
9 Outgoings 63A TP MCCB, 30KA - 10 Nos
10 SMDB(TYPE 3) Qty: 30 Nos

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet3

Or is there anything above it?

Yes bro..Exactly...perfect...I don't know how to insert image of Excel sheet in this forum. Sorry... I tried several way. But I Can't....

Thanks bro..

Fluff · Jul 5, 2018

Is the sheet protected?
Do you have any merged cells?

jjuliet115 · Jul 5, 2018

No bro...all normal only...

Fluff · Jul 5, 2018

In that case I've no idea why you get that error. With the data shown in post#6 I get

Excel 2013/2016

A

B

C

D

E

F

1

SMDB(TYPE 1)

Qty: 10 Nos

ncomer

Outgoings

2

10W TPN

200A TP MCCB, 36KA - 1 No

40A TP MCCB, 30KA - 6 Nos

63A TP MCCB, 30KA - 4 Nos

3

SMDB(TYPE 2)

Qty: 15 Nos

ncomer

Outgoings

4

10W TPN

160A TP MCCB, 36KA - 1 No

63A TP MCCB, 30KA - 10 Nos

5

SMDB(TYPE 3)

Qty: 30 Nos

Sheet3

How to Paste Special Transpose selected range by VBA until data end?

jjuliet115

New Member

Excel Facts

Fluff

MrExcel MVP, Moderator

jjuliet115

New Member

Fluff

MrExcel MVP, Moderator

jjuliet115

New Member

Fluff

MrExcel MVP, Moderator

jjuliet115

New Member

Fluff

MrExcel MVP, Moderator

jjuliet115

New Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

How to Paste Special Transpose selected range by VBA until data end?

New Member

Excel Facts

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock