VBA: shift data in a range towards left

[Nelson78]

Hello everybody.

I have a variable range defined as set = range1 in which there are empty cells.

I need, for each row in the range, to shift all not empty cells toward the left.
The operation has not to impact the columns on the right of the range, because they contains other data.
For example, range C3:L28, column from N to the right have to remain at their place.

How can I manage the issue?

Thank's.

 

[Nelson78]

Hello everybody.

I have a variable range defined as set = range1 in which there are empty cells.

I need, for each row in the range, to shift all not empty cells toward the left.
The operation has not to impact the columns on the right of the range, because they contains other data.
For example, range C3:L28, column from N to the right have to remain at their place.

How can I manage the issue?

Thank's.

More precisely, I mean:

set range1 = Range(...........)

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG12Jul08
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Rw [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("C3:M28")
Application.ScreenUpdating = False
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Rows
    ReDim Ray(1 To Rng.Columns.Count)
    c = 0
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Rw [COLOR="Navy"]In[/COLOR] Dn.Cells
        [COLOR="Navy"]If[/COLOR] Not IsEmpty(Rw.Value) [COLOR="Navy"]Then[/COLOR]
           c = c + 1
           Ray(c) = Rw.Value
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Rw
Dn.Cells.ClearContents
Dn(1).Resize(, c).Value = Ray
[COLOR="Navy"]Next[/COLOR]
Application.ScreenUpdating = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Nelson78]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG12Jul08
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Rw [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("C3:M28")
Application.ScreenUpdating = False
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Rows
    ReDim Ray(1 To Rng.Columns.Count)
    c = 0
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Rw [COLOR="Navy"]In[/COLOR] Dn.Cells
        [COLOR="Navy"]If[/COLOR] Not IsEmpty(Rw.Value) [COLOR="Navy"]Then[/COLOR]
           c = c + 1
           Ray(c) = Rw.Value
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Rw
Dn.Cells.ClearContents
Dn(1).Resize(, c).Value = Ray
[COLOR="Navy"]Next[/COLOR]
Application.ScreenUpdating = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

Excel VBA run-time error 1004 : Application-defined or object-defined error

on the following line:

Dn(1).Resize(, c).Value = Ray

 

[MickG]

Have you got any blank rows ????

 

[Nelson78]

Have you got any blank rows ????

Yes. The range in yellow is where we are working on.

https://imgur.com/a/fVgBdfF

 

[MickG]

Add lines as shown in red:-

Next Rw
[B][COLOR=#ff0000]If c > 0 Then[/COLOR][/B]
    Dn.Cells.ClearContents
    Dn(1).Resize(, c).Value = Ray
[B][COLOR=#ff0000]End If[/COLOR][/B]
Next

 

[Nelson78]

Add lines as shown in red:-

Next Rw
[B][COLOR=#ff0000]If c > 0 Then[/COLOR][/B]
    Dn.Cells.ClearContents
    Dn(1).Resize(, c).Value = Ray
[B][COLOR=#ff0000]End If[/COLOR][/B]
Next

Yes, now the process reaches the end instead of having an error.

Yet, something strange happens to the dates. If you see the image, on each single row the scheme is always date followed by text. The yellow area is entirely formatted "general".

At the end of the process, some data assumes the format "date" for "general", but this date is incoherent because is a futur date (for example 08 january 2018 becomes 01 august 2018, and this is not possible because the date refers to event already happened).

How can I manage this issue about date?

Thank's.

 

[MickG]

Can you post your data as an excel file rather than a picture.

 

[Nelson78]

Wrong message, not to consider.