VBA Shift all data to left alternative Delete

[hajiali]

Hello everyone need help to shifting all data in range to far left leaving no blank cells. I need an alternative to .Delete shift:=xlToLeft as this effects all data to the right.

my range("AG2:AZ1000") I do not want to effect the data to the right of column AZ.

ex. below Orange Highlight is my data and Green Highlight is what I want the outcome to be

Book2
	AG	AH	AI	AJ	AK	AL
1	D1	D2	D3	D4	D5	D6
2			7/27	7/28	7/31
3
4
5		7/5		7/7	7/8
6		7/14	7/17	7/18
7				7/3		7/9
8
9
10
11
12
13
14	D1	D2	D3	D4	D5	D6
15	7/27	7/28	7/31
16
17
18	7/5	7/7	7/8
19	7/14	7/17	7/18
20	7/3	7/9
21
22
23
24
25
Sheet1

any help is greatly appreciated.

 

[Akuini]

Try this:

Sub hajiali_1()
Dim i As Long, j As Long, k As Long
Dim va

    va = Range("AG2:AZ1000")
    ReDim vb(1 To UBound(va, 1), 1 To UBound(va, 2))
    
    For i = 1 To UBound(va, 1)
       k = 0
           For j = 1 To UBound(va, 2)
               If Len(va(i, j)) <> 0 Then
                   k = k + 1
                   vb(i, k) = va(i, j)
               End If
           Next
    Next
    
    Range("AG2").Resize(UBound(vb, 1), UBound(vb, 2)) = vb
End Sub

 

[hajiali]

Thanks Akuini code works great

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.