Fill down formula to end

Lacherlich

New Member
Joined
Dec 2, 2014
Messages
12
Hi

I know this question has been asked in many guises, but the answers don't seem to help and are all specific to the question.

Basically, I am copying data from varying spread sheets into one worksheet. Each time I paste the data into the spread sheet I want to label each data set depending on which sheet I copied it from.

I.E there is a column where I would label the data from one spreadie as '20' for example.

I am having trouble though to get the VBA to fill down the column next to the copied data so all cells are labelled as '20'.

The vba I tried is below, but not complete:

Sub endcellselect()

Range("c1").End(xlDown).Select (this was my attempt to highlight the row of the last blank cell in the label column)Dim d As String
d = ActiveCell.Row


Range("d1").End(xlDown).Offset(1, 0).Select (this was to highlight the first blank cell in the column so I could then select the range to ='20')Dim c As String
c = ActiveCell.Row

Range("d" & c).Select
Selection.Value = 20

Selection.AutoFill Destination:=Range("activecell:D & d")
Range("D3:D6").Select


Range(ActiveCell, ActiveCell & d & "").End(xlDown).Value = 20



Hmm, I hope that kinda makes sense what im trying to achieve? Any help would be great,

Thanks
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

netuser

Active Member
Joined
Jun 19, 2015
Messages
420
Can you clarify something.

You want to copy Data in Column C from first Empty Cell to Last used Cell (based on Column D ) ? Right ?

If the answer is yes, here you go :



Code:
Dim found As Range, FECell, LECell

'Lets find first Empty cell
Set found = Columns("C").Find(What:=vbNullString, LookIn:=xlValues, LookAt:=xlPart)
FECell = found.Row


'Lets find last used cell in Column D
LECell = Range("D" & Rows.Count).End(xlUp).Row


'Lets copy data from Fist empty to last cell
With Range("C" & FECell & ":C" & LECell)
.Value = "20"
End With


<b>Excel 2010 32 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">X</td><td style=";">dsf</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">X</td><td style=";">fsdf</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">X</td><td style=";">fsdf</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Y</td><td style=";">fsdf</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">YRT</td><td style=";">fds</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Y</td><td style=";">sd</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">C</td><td style=";">dsfds</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">C</td><td style=";">sdsd</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">D</td><td style=";">sdf</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">20</td><td style=";">dfsdfds</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">20</td><td style=";">sdfsd</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">20</td><td style=";">sdfsdfsd</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">20</td><td style=";">sdfsd</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">20</td><td style=";">sdfsd</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">20</td><td style=";">sdfsd</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">20</td><td style=";">sfdf</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">20</td><td style=";">sfds</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120"></p><br /><br />







Hi

I know this question has been asked in many guises, but the answers don't seem to help and are all specific to the question.

Basically, I am copying data from varying spread sheets into one worksheet. Each time I paste the data into the spread sheet I want to label each data set depending on which sheet I copied it from.

I.E there is a column where I would label the data from one spreadie as '20' for example.

I am having trouble though to get the VBA to fill down the column next to the copied data so all cells are labelled as '20'.

The vba I tried is below, but not complete:

Sub endcellselect()

Range("c1").End(xlDown).Select (this was my attempt to highlight the row of the last blank cell in the label column)Dim d As String
d = ActiveCell.Row


Range("d1").End(xlDown).Offset(1, 0).Select (this was to highlight the first blank cell in the column so I could then select the range to ='20')Dim c As String
c = ActiveCell.Row

Range("d" & c).Select
Selection.Value = 20

Selection.AutoFill Destination:=Range("activecell:D & d")
Range("D3:D6").Select


Range(ActiveCell, ActiveCell & d & "").End(xlDown).Value = 20



Hmm, I hope that kinda makes sense what im trying to achieve? Any help would be great,

Thanks
 

Lacherlich

New Member
Joined
Dec 2, 2014
Messages
12
You absolute genius!

I just modified what you put slightly and it works perfectly.

Many thanks, much appreciated!
 

candybg

Board Regular
Joined
Jan 18, 2014
Messages
111
A simple solution that might work for you would be to just type what you want, ie 20, in the first cell of the column that you want filled in. Then click one time in the cell to activate a indicator ring around the cell. The very bottom right hand corner has a tiny square. If you position your cursor exactly on that little square, your cursor changes shape to a PLUS SIGN. When you see the plus sign, DOUBLE-CLICK it and it will fill in your column to the end of your data with that text or number.

FYI - typing these instructions takes MUCH longer than just doing it : )

Hope this helps!
 

Lacherlich

New Member
Joined
Dec 2, 2014
Messages
12
Hey

Thanks, but needed it be automated within the macro as trying to cut down the time it takes to compile the data manually.

Chers
 

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
A couple of other ways, but probably not any faster.

Howard


Code:
Sub x20_CD1()

Dim cRow As Long, dRow As Long

cRow = Cells(Rows.Count, "C").End(xlUp).Row
dRow = Cells(Rows.Count, "D").End(xlUp).Row

Range("C" & cRow + 1).Resize(dRow - cRow, 1) = 20

End Sub


Code:
Sub x20_CD2()

Dim cRow As Long, dRow As Long

cRow = Cells(Rows.Count, "C").End(xlUp).Row
dRow = Cells(Rows.Count, "D").End(xlUp).Row

With Range("C" & cRow + 1 & ":" & "C" & dRow)
 .Formula = "=20": .Value = .Value
End With

End Sub
 

Forum statistics

Threads
1,137,151
Messages
5,679,895
Members
419,861
Latest member
AceDaMace

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top