Fill Right

[vini1508]

Hii,
We can autofill columns by double clicking on them till the last active row...it is done downwards.. i want a macro that will fill to right but on click of button not by dragging..
for example:-
If in sheet, in 1st row i have inserted 1,2,3,4
and in 2nd row if i insert only 3 and 6
and in 3rd row if i inserted only March
given below

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]March[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

after clicking on macro i want that it should auto fill the rows upto the the last column i.e column D


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]9[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]March[/TD]
[TD]April[/TD]
[TD]May[/TD]
[TD]June[/TD]
[/TR]
</tbody>[/TABLE]



I dont have any idea how to achive his pls help me

 

[vini1508]

That's a good start.

That isn't surprising as I wrote the code for the information that you gave. Remember, I am not looking at your worksheet, so I can only use what information you give.

So where does the data start?
- What is the first column?
- what row do we use to work out how far right to fill (the header row)?
- what is the first row with actual data to fill right?

1. the data can be started from any cell..
2.the user will select the rows for right fill.. and after clicking button it should fill the data till last column based on starting row...

 

[Peter_SSs]

2.the user will select the rows for right fill..

Will their selection include the header row? That is, for the data in post #1, would they select from cell A1 down to cell A3?

If they will not be selecting the header row, then can we assume that the header row is the row immediately above the selection?

 

[vini1508]

no... header row will not be selected...

 

[Peter_SSs]

no... header row will not be selected...

So..

If they will not be selecting the header row, then can we assume that the header row is the row immediately above the selection?

 

[vini1508]

yes the header row is the row immediately above the selection...

 

[Peter_SSs]

yes the header row is the row immediately above the selection...

In that case, give this a try

Sub FR()
  Dim cols As Long
  Dim r As Range
  
  Application.ScreenUpdating = False
  With Selection
    cols = Cells(.Row - 1, Columns.Count).End(xlToLeft).Column - .Column + 1
    If .Columns.Count = 1 And .Areas.Count = 1 And cols > 1 Then
      For Each r In Selection
        On Error GoTo PreExit
        Range(r, r.Offset(, cols).End(xlToLeft)).AutoFill Destination:=r.Resize(, cols)
      Next r
    End If
  End With
PreExit:
  On Error GoTo 0
  Application.ScreenUpdating = True
End Sub

 

[vini1508]

thank u so much Peter_SSs...
the code works perfect...
what can i do if in selection i have empty columns and i want it should not auto fill that row...

 

[vini1508]

i have inserted if statement to check empty values and it works fine... thank u so much for all your help...