Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic range adjustment when copying data
Excel experts... I have two worksheets in my workbook, one of which goes to a maste worksheet and selects a range of cells. The macro I use is: ActiveCell.FormulaR1C1 = "=Dates!RC" Range("A2").Select Selection.AutoFill Destination:=Range("A2:A500") Type:=xlFillDefault Range("A2:A500").Select I use a range from 2 to 500 because the information rarely exceeds 50 cells. How can I have the macro go to the main worksheet and onl collect what data is there rather than just 500 rows of data, most o which I don't use and look ugly in the worksheet. Many thanks for your help. This site and the people on it is a savior -- Sandema ----------------------------------------------------------------------- Sandeman's Profile: http://www.excelforum.com/member.php...fo&userid=3244 View this thread: http://www.excelforum.com/showthread.php?threadid=52311 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic range adjustment when copying data
Dim rng as Range
With Worksheets("Dates") set rng = .Range(.Cells(2,1),.Cells(2,1).End(xldown)) End with with Worksheets("Master") .Range(rng.Address).FormulaR1C1 = "=Dates!RC" end with -- Regards, Tom Ogilvy "Sandeman" wrote: Excel experts... I have two worksheets in my workbook, one of which goes to a master worksheet and selects a range of cells. The macro I use is: ActiveCell.FormulaR1C1 = "=Dates!RC" Range("A2").Select Selection.AutoFill Destination:=Range("A2:A500"), Type:=xlFillDefault Range("A2:A500").Select I use a range from 2 to 500 because the information rarely exceeds 500 cells. How can I have the macro go to the main worksheet and only collect what data is there rather than just 500 rows of data, most of which I don't use and look ugly in the worksheet. Many thanks for your help. This site and the people on it is a savior. -- Sandeman ------------------------------------------------------------------------ Sandeman's Profile: http://www.excelforum.com/member.php...o&userid=32440 View this thread: http://www.excelforum.com/showthread...hreadid=523117 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatic adjustment | Excel Discussion (Misc queries) | |||
automatic field adjustment | Excel Programming | |||
Automatic row height adjustment | Excel Discussion (Misc queries) | |||
Automatic column width adjustment | Excel Worksheet Functions | |||
Automatic copying of data cells | Excel Discussion (Misc queries) |