Fill down formula to end

[Lacherlich]

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

 

[netuser]

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 :

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

Excel 2010 32 bit
	C	D
1	X	dsf
2	X	fsdf
3	X	fsdf
4	Y	fsdf
5	YRT	fds
6	Y	sd
7	C	dsfds
8	C	sdsd
9	D	sdf
10	20	dfsdfds
11	20	sdfsd
12	20	sdfsdfsd
13	20	sdfsd
14	20	sdfsd
15	20	sdfsd
16	20	sfdf
17	20	sfds
Sheet1

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]

You absolute genius!

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

Many thanks, much appreciated!

 

[candybg]

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]

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]

A couple of other ways, but probably not any faster.

Howard

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

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