3 Actions VBA Copy, Clear contents ("") and Shift all to left

motilulla · Nov 10, 2018

Hello,

Below example columns C:K result are by formulas, all formulas have not an outcome; in that case the formula has leaved a cell blank with (""). 

Now I need a VBA code that does 3 actions...
1-Copy values of columns C:K into M:U
2- Clear contents ("") of columns M:U
3-Shift all values to left

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

1

2

3

4

5

n1

n2

n3

n4

n5

n6

n7

n8

n9

n1

n2

n3

n4

n5

n6

n7

n8

n9

6

2

1

2

1

7

2

1

2

1

2

8

2

1

2

1

9

1

3

1

3

10

2

3

1

2

3

1

11

2

1

2

1

12

2

3

2

3

2

13

2

1

2

1

14

1

3

1

3

15

2

1

3

1

2

1

3

1

16

1

2

1

2

1

2

1

2

17

2

3

2

3

2

18

4

1

4

1

19

2

1

2

1

2

1

2

1

20

2

1

2

1

21

2

1

2

1

2

1

2

1

22

2

1

2

1

2

1

2

1

23

3

1

3

1

24

2

1

2

1

25

6

1

6

1

26

3

2

1

3

2

1

27

2

1

2

1

2

28

1

2

1

2

29

1

2

1

2

1

2

1

2

1

30

3

2

1

3

2

1

31

6

1

6

1

32

1

3

2

1

3

2

1

33

1

2

1

2

1

34

3

1

2

1

3

1

2

1

35

1

2

1

2

1

36

2

1

2

1

2

37

3

1

2

3

1

2

38

3

1

3

1

39

2

1

2

1

2

40

1

2

1

2

1

41

3

1

2

3

1

2

42

1

2

1

2

1

2

1

2

1

43

2

1

2

1

2

1

2

1

44

2

1

2

1

45

4

1

4

1

46

4

1

4

1

47

4

1

4

1

48

3

1

2

3

1

2

49

2

3

1

2

3

1

50

4

1

4

1

51

1

2

1

2

1

52

4

1

4

1

53

2

1

2

1

54

2

1

2

1

55

1

3

1

3

1

56

1

2

1

2

1

57

2

1

2

1

58

4

1

4

1

59

5

2

5

2

60

1

2

1

2

1

2

1

2

1

Sheet8

Thank you all

Excel 2000
Regards,
Moti

Rick Rothstein · Nov 10, 2018

Does this do what you want...

Code:

[table="width: 500"]
[tr]
	[td]Sub motilulla()
  With Range("C6:K" & Cells.Find("*", , xlValues, , xlRows, xlPrevious, , , False).Row)
    .Offset(, 10).Value = .Value
    .Offset(, 10).SpecialCells(xlBlanks).Delete xlShiftToLeft
  End With
End Sub[/td]
[/tr]
[/table]

motilulla · Nov 10, 2018

Rick Rothstein said:
Does this do what you want...

Hello Rick, code highlight ".Find" and give the message that numbers of arguments are not correct

Thank you

Regards,
Moti

MARK858 · Nov 10, 2018

motilulla said:
Hello Rick, code highlight ".Find" and give the message that numbers of arguments are not correct

Thank you

Regards,
Moti

Hi motilulla, did you copy/paste the code or re-write it?

motilulla · Nov 10, 2018

motilulla said:
Hello Rick, code highlight ".Find" and give the message that numbers of arguments are not correct

Thank you

Regards,
Moti

Hello Rick, removing 2 coma after the xlPrevious as below the code worked fine

Code:

Sub motilulla()
  
  'With Range("C6:K" & Cells.Find("*", , xlValues, , xlRows, xlPrevious,[B][COLOR=#ff0000] , ,[/COLOR][/B] False).Row)
   
   With Range("C6:K" & Cells.Find("*", , xlValues, , xlRows, xlPrevious, False).Row)
    .Offset(, 10).Value = .Value
    .Offset(, 10).SpecialCells(xlBlanks).Delete xlShiftToLeft
   End With
  
End Sub

Thank you for your help

Regards,
Moti

motilulla · Nov 10, 2018

MARK858 said:
Hi motilulla, did you copy/paste the code or re-write it?

Hello MARK858, I did copy/paste the code

Regards,
Moti

MARK858 · Nov 10, 2018

I think the issue shows up in your signature block in your first post.

Excel 2000
Regards,
Moti

I am pretty sure back then MatchByte didn't exist, not sure what other parameter didn't. Probably SearchFormat at a guess. It's a very old version now and so documentation is scarce.

motilulla · Nov 10, 2018

MARK858 said:
I think the issue shows up in your signature block in your first post.

Excel 2000
Regards,
Moti

I am pretty sure back then MatchByte didn't exist, not sure what other parameter didn't. Probably SearchFormat at a guess. It's a very old version now and so documentation is scarce.

Hello MARK858, yes it is too old version, but removing the 2 comas Rick code worked fine no problem.

But now I come up with new trouble after running the code, in the column V insert the formula =M6&" | "&N6&" | "&O6&" | "&P6&" | "&Q6&" | "&R6&" | "&S6&" | "&T6&" | "&U6, sheet result look like perfect

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

1

2

3

4

5

n1

n2

n3

n4

n5

n6

n7

n8

n9

n1

n2

n3

n4

n5

n6

n7

n8

n9

Join

6

2

1

2

1

2 | 2 | 2 | 1 | | | | |

7

2

1

2

1

2

2 | 2 | 1 | 2 | | | | |

8

2

1

2

1

2 | 2 | 2 | 1 | | | | |

9

1

3

1

3

1 | 1 | 1 | 1 | 3 | | | |

10

2

3

1

2

3

1

2 | 3 | 1 | 1 | | | | |

11

2

1

2

1

2 | 1 | 1 | 1 | 1 | 1 | | |

12

2

3

2

3

2

2 | 3 | 2 | | | | | |

13

2

1

2

1

2 | 2 | 1 | 1 | 1 | | | |

14

1

3

1

3

1 | 1 | 1 | 1 | 3 | | | |

15

2

1

3

1

2

1

3

1

2 | 1 | 3 | 1 | | | | |

16

1

2

1

2

1

2

1

2

1 | 2 | 1 | 1 | 2 | | | |

17

2

3

2

3

2

2 | 3 | 2 | | | | | |

18

4

1

4

1

4 | 1 | 1 | 1 | | | | |

19

2

1

2

1

2

1

2

1

2 | 1 | 2 | 1 | 1 | | | |

20

2

1

2

1

2 | 1 | 1 | 1 | 1 | 1 | | |

21

2

1

2

1

2

1

2

1

2 | 1 | 1 | 2 | 1 | | | |

22

2

1

2

1

2

1

2

1

2 | 1 | 1 | 2 | 1 | | | |

23

3

1

3

1

3 | 1 | 1 | 1 | 1 | | | |

24

2

1

2

1

2 | 2 | 1 | 1 | 1 | | | |

25

6

1

6

1

6 | 1 | | | | | | |

26

3

2

1

3

2

1

3 | 2 | 1 | 1 | | | | |

27

2

1

2

1

2

2 | 1 | 2 | 2 | | | | |

28

1

2

1

2

1 | 2 | 2 | 2 | | | | |

29

1

2

1

2

1

2

1

2

1

1 | 2 | 1 | 2 | 1 | | | |

30

3

2

1

3

2

1

3 | 2 | 1 | 1 | | | | |

31

6

1

6

1

6 | 1 | | | | | | |

32

1

3

2

1

3

2

1

1 | 3 | 2 | 1 | | | | |

33

1

2

1

2

1

1 | 2 | 1 | 1 | 1 | 1 | | |

34

3

1

2

1

3

1

2

1

3 | 1 | 2 | 1 | | | | |

35

1

2

1

2

1

1 | 2 | 2 | 1 | 1 | | | |

36

2

1

2

1

2

2 | 1 | 1 | 1 | 2 | | | |

37

3

1

2

3

1

2

3 | 1 | 1 | 2 | | | | |

38

3

1

3

1

3 | 1 | 1 | 1 | 1 | | | |

39

2

1

2

1

2

2 | 1 | 2 | 2 | | | | |

40

1

2

1

2

1

1 | 2 | 2 | 1 | 1 | | | |

41

3

1

2

3

1

2

3 | 1 | 1 | 2 | | | | |

42

1

2

1

2

1

2

1

2

1

1 | 2 | 1 | 2 | 1 | | | |

43

2

1

2

1

2

1

2

1

2 | 1 | 1 | 2 | 1 | | | |

44

2

1

2

1

2 | 2 | 1 | 1 | 1 | | | |

45

4

1

4

1

4 | 1 | 1 | 1 | | | | |

46

4

1

4

1

4 | 1 | 1 | 1 | | | | |

47

4

1

4

1

4 | 1 | 1 | 1 | | | | |

48

3

1

2

3

1

2

3 | 1 | 1 | 2 | | | | |

49

2

3

1

2

3

1

2 | 3 | 1 | 1 | | | | |

50

4

1

4

1

4 | 1 | 1 | 1 | | | | |

51

1

2

1

2

1

1 | 2 | 2 | 1 | 1 | | | |

52

4

1

4

1

4 | 1 | 1 | 1 | | | | |

53

2

1

2

1

2 | 1 | 1 | 1 | 1 | 1 | | |

54

2

1

2

1

2 | 1 | 1 | 1 | 1 | 1 | | |

55

1

3

1

3

1

1 | 1 | 1 | 3 | 1 | | | |

56

1

2

1

2

1

1 | 1 | 2 | 2 | 1 | | | |

57

2

1

2

1

2 | 1 | 1 | 1 | 1 | 1 | | |

58

4

1

4

1

4 | 1 | 1 | 1 | | | | |

59

5

2

5

2

5 | 2 | | | | | | |

60

1

2

1

2

1

2

1

2

1

1 | 2 | 1 | 2 | 1 | | | |

Sheet8

When I re rum the code the sheet change as below why the column V shifted to left?

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

1

2

3

4

5

n1

n2

n3

n4

n5

n6

n7

n8

n9

n1

n2

n3

n4

n5

n6

n7

n8

n9

Join

6

2

1

2

1

#REF!

7

2

1

2

1

2

#REF!

8

2

1

2

1

#REF!

9

1

3

1

3

#REF!

10

2

3

1

2

3

1

#REF!

11

2

1

2

1

#REF!

12

2

3

2

3

2

#REF!

13

2

1

2

1

#REF!

14

1

3

1

3

#REF!

15

2

1

3

1

2

1

3

1

#REF!

16

1

2

1

2

1

2

1

2

#REF!

17

2

3

2

3

2

#REF!

18

4

1

4

1

#REF!

19

2

1

2

1

2

1

2

1

#REF!

20

2

1

2

1

#REF!

21

2

1

2

1

2

1

2

1

#REF!

22

2

1

2

1

2

1

2

1

#REF!

23

3

1

3

1

#REF!

24

2

1

2

1

#REF!

25

6

1

6

1

#REF!

26

3

2

1

3

2

1

#REF!

27

2

1

2

1

2

#REF!

28

1

2

1

2

#REF!

29

1

2

1

2

1

2

1

2

1

#REF!

30

3

2

1

3

2

1

#REF!

31

6

1

6

1

#REF!

32

1

3

2

1

3

2

1

#REF!

33

1

2

1

2

1

#REF!

34

3

1

2

1

3

1

2

1

#REF!

35

1

2

1

2

1

#REF!

36

2

1

2

1

2

#REF!

37

3

1

2

3

1

2

#REF!

38

3

1

3

1

#REF!

39

2

1

2

1

2

#REF!

40

1

2

1

2

1

#REF!

41

3

1

2

3

1

2

#REF!

42

1

2

1

2

1

2

1

2

1

#REF!

43

2

1

2

1

2

1

2

1

#REF!

44

2

1

2

1

#REF!

45

4

1

4

1

#REF!

46

4

1

4

1

#REF!

47

4

1

4

1

#REF!

48

3

1

2

3

1

2

#REF!

49

2

3

1

2

3

1

#REF!

50

4

1

4

1

#REF!

51

1

2

1

2

1

#REF!

52

4

1

4

1

#REF!

53

2

1

2

1

#REF!

54

2

1

2

1

#REF!

55

1

3

1

3

1

#REF!

56

1

2

1

2

1

#REF!

57

2

1

2

1

#REF!

58

4

1

4

1

#REF!

59

5

2

5

2

#REF!

60

1

2

1

2

1

2

1

2

1

#REF!

Sheet9

Does it can be solved? 

Regards,
Moti

MARK858 · Nov 10, 2018

If you Delete/Shift cells to the left then everything to the right shifts. The easiest way around it would be to clear column V at the start of the macro then insert the formula with code at the end of the macro.

motilulla · Nov 10, 2018

MARK858 said:
If you Delete/Shift cells to the left then everything to the right shifts. The easiest way around it would be to clear column V at the start of the macro then insert the formula with code at the end of the macro.

Thank you MARK858, for the suggestion got it that could be the possible solution. 

Would be any formula solution that can be use in the column M:U which can group the values ? 

Regards,
Moti

3 Actions VBA Copy, Clear contents ("") and Shift all to left

motilulla

Well-known Member

Excel Facts

Rick Rothstein

MrExcel MVP

motilulla

Well-known Member

MARK858

MrExcel MVP

motilulla

Well-known Member

motilulla

Well-known Member

MARK858

MrExcel MVP

motilulla

Well-known Member

MARK858

MrExcel MVP

motilulla

Well-known Member

Similar threads

Forum statistics

Share this page

3 Actions VBA Copy, Clear contents ("") and Shift all to left

Well-known Member

Excel Facts

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

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