Vba move data

Panoos64 · Apr 15, 2019

Hi all, I would like to use a VBA code so that, to move the data numbers from col. “C4” to “P” to the parallel descriptions’ row which is offset 2 rows up and right offset 4 columns. I present below the original data and the expected result .

Apologies for my non formal tables

Thank you all in advance

Original data

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

4

SPIRITS

VODKA 75cl

5

6

25.25

35.28

40.52

35.96

63.78

40.74

35.36

78.12

36.45

41.25

78.12

31.26

542.09

7

SPIRITS

WHISKY 75cl

8

9

15.45

28.45

15.48

10.78

25.36

28.96

17.89

16.98

13.75

45.12

35.96

29.36

283.54

Expected result

	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
4		SPIRITS	VODKA 75cl	25.25	35.28	40.52	35.96	63.78	40.74	35.36	78.12	36.45	41.25	78.12	31.26	542.09	542.09
5
6
7		SPIRITS	WHISKY 75cl	15.45	28.45	15.48	10.78	25.36	28.96	17.89	16.98	13.75	45.12	35.96	29.36	283.54	283.54
8

Fluff · Apr 15, 2019

How about

Code:

Sub panoose()
   Dim Cl As Range
   For Each Cl In Range("C:C").SpecialCells(xlConstants)
      Cl.Resize(, 14).Cut Cl.Offset(-2, 3)
   Next Cl
End Sub

Brombrough · Apr 15, 2019

Hi Panoos64 ,

Try this. Make sure you take copy of sheet first just in case

Please don't forget to post Thanks/ Like if this meets your requirements.

Code:

Sub OrganiseSheet()    Dim ws As Worksheet
    Dim lngMaxRow As Long
    
    Set ws = ThisWorkbook.ActiveSheet
    
    lngMaxRow = ws.Range("C" & ws.Rows.Count).End(xlUp).Row






    For I = lngMaxRow To 1 Step -1


        If ws.Range("C" & I).Value <> "" Then
            ws.Range(ws.Range("C" & I), ws.Range("P" & I)).Copy ws.Range("C" & I).Offset(-2, 2)
            ws.Range(ws.Range("C" & I), ws.Range("P" & I)).ClearContents
        End If




    Next I


    Set ws = Nothing
End Sub

Panoos64 · Apr 15, 2019

Hi Fluff, the code stops at this point Cl.Resize(, 14).Cut Cl.Offset(-2, 3) but i couldn't find the reason. It would much appreciate if you could revise it. Therefore that on the the above 14, range of columns include also blank cells.
Thank you once again for your support

Fluff · Apr 15, 2019

What is the first row with data in col C?

Panoos64 · Apr 15, 2019

Hi Brombrough, it works somehow but it stops where the cells are blank, ws.Range(ws.Range("C" & I), ws.Range("P" & I)).Copy ws.Range("C" & I).Offset(-2, 2). Kindly note that from col. "C" : "P" exists also blank cells and as i found where the code detects blank cells it stops. However, the code works and sort the data where the cells are fulfill and ignore the rows where exists empty cells.
I appreciated so much your work and i say a great thanks to you. Hv a nice day

Panoos64 · Apr 16, 2019

Hi Fluff, ok the point is that i had description in cell "C3", i clear it and the code works. However It adapts the data except where in col. "C", "D", e.t.c, the cells are blank it doesn't adapt the data. I would much appreciated if you could modified a little bit the code so that to valid blank cells too. Thank u so much for your support so that and your intention to resolve my project. Hv great day

Fluff · Apr 16, 2019

Can you please post some data that is more representative of your actual data.

Panoos64 · Apr 16, 2019

Hi Fluff, due to lot of data, i present an extract by which appears in col. "C" blank cells which causes the issue. Thank u for your support

[FONT=&quot]C[/FONT]

[FONT=&quot]D[/FONT]

[FONT=&quot]E[/FONT]

[FONT=&quot]F[/FONT]

[FONT=&quot]G[/FONT]

[FONT=&quot]H[/FONT]

[FONT=&quot]I[/FONT]

[FONT=&quot]J[/FONT]

[FONT=&quot]K[/FONT]

[FONT=&quot]L[/FONT]

[FONT=&quot]M[/FONT]

[FONT=&quot]N[/FONT]

[FONT=&quot]O [/FONT]

[FONT=&quot]P[/FONT]

[FONT=&quot]Q[/FONT]

[FONT=&quot]R[/FONT]

[FONT=&quot]S[/FONT]

[FONT=&quot]3[/FONT]

[FONT=&quot]MONTH[/FONT]

[FONT=&quot]JAN[/FONT]

[FONT=&quot]FEB[/FONT]

[FONT=&quot]MAR[/FONT]

[FONT=&quot]APR[/FONT]

[FONT=&quot]MAY[/FONT]

[FONT=&quot]JUN[/FONT]

[FONT=&quot]JUL[/FONT]

[FONT=&quot]AUG[/FONT]

[FONT=&quot]SEP[/FONT]

[FONT=&quot]OCT[/FONT]

[FONT=&quot]NOV[/FONT]

[FONT=&quot]DEC[/FONT]

[FONT=&quot]TOTAL[/FONT]

[FONT=&quot]SYSTEM[/FONT]

[FONT=&quot]4[/FONT]

[FONT=&quot]SPIRITS [/FONT]

[FONT=&quot]VODKA 75cl[/FONT]

[FONT=&quot]5[/FONT]

[FONT=&quot]6[/FONT]

[FONT=&quot]25.25[/FONT]

[FONT=&quot]35.28[/FONT]

[FONT=&quot]40.52[/FONT]

[FONT=&quot]35.96[/FONT]

[FONT=&quot]63.78[/FONT]

[FONT=&quot]40.74[/FONT]

[FONT=&quot]35.36[/FONT]

[FONT=&quot]78.12[/FONT]

[FONT=&quot]36.45[/FONT]

[FONT=&quot]41.25[/FONT]

[FONT=&quot]78.12[/FONT]

[FONT=&quot]31.26[/FONT]

[FONT=&quot]542.09[/FONT]

[FONT=&quot]7[/FONT]

[FONT=&quot]SPIRITS [/FONT]

[FONT=&quot]WHISKY 75cl[/FONT]

[FONT=&quot]8[/FONT]

[FONT=&quot]9[/FONT]

[FONT=&quot]15.45[/FONT]

[FONT=&quot]28.45[/FONT]

[FONT=&quot]15.48[/FONT]

[FONT=&quot]10.78[/FONT]

[FONT=&quot]25.36[/FONT]

[FONT=&quot]28.96[/FONT]

[FONT=&quot]17.89[/FONT]

[FONT=&quot]16.98[/FONT]

[FONT=&quot]13.75[/FONT]

[FONT=&quot]45.12[/FONT]

[FONT=&quot]35.96[/FONT]

[FONT=&quot]29.36[/FONT]

[FONT=&quot]283.54[/FONT]

[FONT=&quot]10[/FONT]

[FONT=&quot]SPIRITS [/FONT]

[FONT=&quot]RED WINES 70cl[/FONT]

[FONT=&quot]11[/FONT]

[FONT=&quot]12[/FONT]

[FONT=&quot]0[/FONT]

[FONT=&quot]25.75[/FONT]

[FONT=&quot]30.56[/FONT]

[FONT=&quot]45.78[/FONT]

[FONT=&quot]12.45[/FONT]

[FONT=&quot]25.78[/FONT]

[FONT=&quot]36.21[/FONT]

[FONT=&quot]40.78[/FONT]

[FONT=&quot]36.96[/FONT]

[FONT=&quot]254.27[/FONT]

[FONT=&quot]283.54[/FONT]

[FONT=&quot]13[/FONT]

[FONT=&quot]SPIRITS [/FONT]

[FONT=&quot]RED WINES 70cl[/FONT]

[FONT=&quot]14[/FONT]

[FONT=&quot]15[/FONT]

[FONT=&quot]78.12[/FONT]

[FONT=&quot]45.98[/FONT]

[FONT=&quot]56.36[/FONT]

[FONT=&quot]14.78[/FONT]

[FONT=&quot]0[/FONT]

[FONT=&quot]25.36[/FONT]

[FONT=&quot]45.52[/FONT]

[FONT=&quot]58.25[/FONT]

[FONT=&quot]25.25[/FONT]

[FONT=&quot]349.62[/FONT]

[FONT=&quot]283.54[/FONT]

Fluff · Apr 16, 2019

Ok, try

Code:

Sub Panoose()
   Dim Cl As Range
   For Each Cl In Range("P4", Range("P" & Rows.Count).End(xlUp)).SpecialCells(xlConstants)
      Cl.Offset(, -13).Resize(, 14).Cut Cl.Offset(-2, -10)
   Next Cl
End Sub

This assumes that col P will always have a value on the lines to be moved

Vba move data

Panoos64

Well-known Member

Excel Facts

Fluff

MrExcel MVP, Moderator

Brombrough

New Member

Panoos64

Well-known Member

Fluff

MrExcel MVP, Moderator

Panoos64

Well-known Member

Panoos64

Well-known Member

Fluff

MrExcel MVP, Moderator

Panoos64

Well-known Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Vba move data

Well-known Member

Excel Facts

MrExcel MVP, Moderator

New Member

Well-known Member

MrExcel MVP, Moderator

Well-known Member

Well-known Member

MrExcel MVP, Moderator

Well-known 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