Drag and Fill Code

[IrishLad]

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Column Header 1	Column Header 2	Column Header 3	Column Header 4	Column Header 5	Column Header 6	Column Header 7	Column Header 8	Column Header 9	Column Header 10	Column Header 11	Column Header 12
2		9			11					11		91	652
3	Before		3		123			9		65
4	Running	10	4							98			36
5	Code							281		25		55	25
6		27	2		89					35
7		6						362				11	18
8		5			17					14		18
9		57	9		240			652		248		175	731
10
11
12		Column Header 1	Column Header 2	Column Header 4	Column Header 7	Column Header 9	Column Header 11	Column Header 12
13		9		11		11	91	652
14	After		3	123	9	65
15	Running	10	4			98		36
16	Code				281	25	55	25
17		27	2	89		35
18		6			362		11	18
19		5		17		14	18
20		57	9	240	652	248	175	731

<tbody>
</tbody>

Sheet1​

The above is for illustration purposes only, cell references are not actual.

I am looking for a piece of code that when ran, will automatically drag and fill into blank columns, columns that actually have data in them.

The actual range is H1501:AP1501, this represents the yellow cells as above, if any cells in this range are blank, the whole column in the blank cell range
should be filled with the first column to the right that is not blank.

The code should account for a sequence of blank cells before encountering a cell with data in the range H1501:AP1501

The column range to be filled would be H1:H1501 , I1:I1501 and so on

I cannot just delete the blank columns as the above scenario runs all down the worksheet and across a far bigger range than the first given
so I will copy and amend the initial code to run in stages as I work down the worksheet.

I'm still completely new to code and an Excel newbie, so please bear that in mind when replying and forgive my lack of "Techspeak"

Thanks In Advance.

 

[MickG]

You can try this:-
This code will deletes columns in the "Usedrange" that have No data after row 2 and returns values as shown in your results . !!

[COLOR="Navy"]Sub[/COLOR] MG01Mar44
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = ActiveSheet.UsedRange
[COLOR="Navy"]For[/COLOR] n = Rng.Columns.Count To 1 [COLOR="Navy"]Step[/COLOR] -1
    [COLOR="Navy"]If[/COLOR] Application.CountA(Cells(2, n).Resize(Rng.Rows.Count)) = 0 [COLOR="Navy"]Then[/COLOR]
         Columns(n).Delete
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[oldbrewer]

fred

dave

harry

3

13

28

4

14

29

5

15

30

text1

text2

text3

text4

text5

text6

text7

text11

text12

text13

text14

text15

text16

text17

text21

text22

text23

text24

text25

text26

text27

text31

text32

text33

text34

text35

text36

text37

text41

text42

text43

text44

text45

text46

text47

text51

text52

text53

text54

text55

text56

text57

text61

text62

text63

text64

text65

text66

text67

macro

For z = 1 To 4

For j = 1 To 6

original data table

For k = 2 To 4

If Cells(k, j) = "" Then Sum = Sum + 1

fred

bill

dave

tom

george

harry

Next k

3

13

28

If Sum = 3 Then GoTo 50 Else GoTo 100

4

14

29

50 Sum = 0

5

15

30

For m = 1 To 4

Cells(m, j) = Cells(m, j + 1)

Cells(m, j + 1) = ""

text1

text2

text3

text4

text5

text6

text7

Next m

text11

text12

text13

text14

text15

text16

text17

100 Sum = 0

text21

text22

text23

text24

text25

text26

text27

Next j

text31

text32

text33

text34

text35

text36

text37

Next z

text41

text42

text43

text44

text45

text46

text47

End Sub

text51

text52

text53

text54

text55

text56

text57

text61

text62

text63

text64

text65

text66

text67

<colgroup><col width="64" span="9" style="width:48pt"> <col width="64" style="width:48pt"> <col width="64" span="8" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[IrishLad]

Can anyone help me here, I don't think the two responders above got my meaning.

Thanks.

 

[IrishLad]

bump

 

[mikerickson]

The code in post #2 seems to do what you want. It takes a different approach than what you suggested. Instead of filling the blank cells with the contents from the right, it simply deletes the blank cells and the filled cells automatically come along. If you want to restrict the action to the first 1501 rows, you could alter it

Sub MG01Mar44
Dim Rng As Range, n As Long
Set Rng = ActiveSheet.UsedRange
For n = Rng.Columns.Count To 1 Step -1
    With Cells(1, n).EntireColumn
        If Application.CountA(.Range("A2:A1500")) = 0 Then
             .Range("A1:A1501).Delete shift:=xlToLeft
        End If
    End With
Next n
End Sub

 

[IrishLad]

Mike, thank you ever so much for replying, I ran the code in post #2, it ran for 5 mins plus, but no drag and fill took place when finished. Now with your code I am getting a compile error, syntax error whatever that is. Please remember that you're dealing with a 53 yr old, new to computing and excel here in the last 6 months.

 

[mikerickson]

I missed a quotation mark

.Range("A1:A1501").Delete shift:=xlToLeft

The 5 minute run time suggests that your UsedRange is much larger than you expect.

 

[IrishLad]

Mike, please read my opening post again. Nowhere in your code does it refer to the range that I specified

Columns A to G are static and don't need to be filled whatsoever.

I want the code to search across the range H1501 to AP1501, if any of these cells are BLANK as in my opening post example (not containing zero or anything else) then the nearest column with data to the blank columns right should be dragged into the blank column

 

[oldbrewer]

col E has become col D in your example

my solution is generic in that it scrutinises cols 1 to 6

I can easily make it look at cols 8 to 42

your original post has clearly confused us

i find blank columns, close them up without disturbing cells lower down