Copydown

[Roman via OfficeKB.com]

I'm trying to write a small macro just to automate an uploading process. Now
I'm not sure of the command to use but I want it to autofill down but only to
where the info stops. For example,
A B C
sum of B&C 1 1

now in A it's going to be 2 but in B&C there could be 29 rows or maybe 100
rows. i need it to autofill A down to where ever B&C stop. Thoughts?

 

[Guest]

Roman,

try something like this

Sub Test

Dim lRow As Long
With ActiveSheet
lRow = Intersect(.UsedRange,
..Range("B:C")).SpecialCells(xlCellTypeLastCell).Row
.Range("A2").Formula = "=SUM(B2:C2)"
.Range("A2:A" & lRow).FillDown
End With

End Sub

 

[Ron de Bruin]

Hi Roman

This example will filldown A1 till the last row with data in B

Sub test()
Dim LastRow As Long
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, "B").End(xlUp).Row
.Range("A1").AutoFill Destination:=.Range("A1:A" & LastRow) _
, Type:=xlFillDefault
End With
End Sub

 

[Roman via OfficeKB.com]

That's perfect!! thanks! one more question. When i use this:

Reponse = InputBox("Please enter Expiration Date")
Range("D1", Range("D1").End(xlDown)) = Response

Nothing happens...

Hi Roman

This example will filldown A1 till the last row with data in B

Sub test()
Dim LastRow As Long
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, "B").End(xlUp).Row
.Range("A1").AutoFill Destination:=.Range("A1:A" & LastRow) _
, Type:=xlFillDefault
End With
End Sub

I'm trying to write a small macro just to automate an uploading process. Now
I'm not sure of the command to use but I want it to autofill down but only to

[quoted text clipped - 4 lines]

now in A it's going to be 2 but in B&C there could be 29 rows or maybe 100
rows. i need it to autofill A down to where ever B&C stop. Thoughts?

 

[Ron de Bruin]

You can use this

Dim rng As Range
Reponse = InputBox("Please enter Expiration Date")
Set rng = Range("D1", Range("D1").End(xlDown))
rng.Value = Reponse



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

That's perfect!! thanks! one more question. When i use this:

Reponse = InputBox("Please enter Expiration Date")
Range("D1", Range("D1").End(xlDown)) = Response

Nothing happens...

Hi Roman

This example will filldown A1 till the last row with data in B

Sub test()
Dim LastRow As Long
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, "B").End(xlUp).Row
.Range("A1").AutoFill Destination:=.Range("A1:A" & LastRow) _
, Type:=xlFillDefault
End With
End Sub

I'm trying to write a small macro just to automate an uploading process. Now
I'm not sure of the command to use but I want it to autofill down but only to

[quoted text clipped - 4 lines]

now in A it's going to be 2 but in B&C there could be 29 rows or maybe 100
rows. i need it to autofill A down to where ever B&C stop. Thoughts?