Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a constant changing Range
Hi all Is it possible to "select" a varying amount of text for copying from a worksheet (sheet1) that will never be the same range? I need excel to !find" the extent of the range for copying every time? Part of the macro I run copies text from "sheet"1 to "sheet 2", but the number of rows is always different in sheet 1 Is this possible using VBA? Thanks for your help. Regards, Michael |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a constant changing Range
MyRange = Worksheets("Sheet1").Cells(Rows.Count, _ 1).End(xlUp).Row Abdul Salam -----Original Message----- Hi all Is it possible to "select" a varying amount of text for copying from a worksheet (sheet1) that will never be the same range? I need excel to !find" the extent of the range for copying every time? Part of the macro I run copies text from "sheet"1 to "sheet 2", but the number of rows is always different in sheet 1 Is this possible using VBA? Thanks for your help. Regards, Michael . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a constant changing Range
Patrick, much appreciated, works great ! -----Original Message----- No. The answer you supplied does not set a range.It merely returns the last row. using a variant type called MyRange is also misleading in this regard as it infers a range. As I understand the questin, the user wants to copy a table from sheet1 to sheet2, but the table length varies. In the code below, a table begins at C5, has three columns and will be copied to sheet2 from cell A1 Sub CopyData() Dim rSource As Range Dim rStart As Range Set rStart = Sheet1.Range("C5") Set rSource = Sheet1.Range(rStart, rStart.End (xlDown).Offset(0, 2)) With Sheet2 .Range(.Range("A1"), _ .Cells(rSource.Rows.Count, _ rSource.Columns.Count)).Value = _ rSource.Value End With End Sub first we set memory for two ranges, one for the start cell (C5) and the other as the source once we determine the table size... Set rSource = Sheet1.Range(rStart, rStart.End (xlDown).Offset(0, 2)) The code above sets the range called rSource to an area defined by the starting cell , then moves down to the end of the column and right two columns Now we have the source range, we know its size from Columns.Count and Rows.Count, which means we can very easily copy it anywhere..in the examople , sheet2. Hope thos is clear. Patrick Molloy Microsoft Excel MVP -----Original Message----- MyRange = Worksheets("Sheet1").Cells(Rows.Count, _ 1).End(xlUp).Row Abdul Salam -----Original Message----- Hi all Is it possible to "select" a varying amount of text for copying from a worksheet (sheet1) that will never be the same range? I need excel to !find" the extent of the range for copying every time? Part of the macro I run copies text from "sheet"1 to "sheet 2", but the number of rows is always different in sheet 1 Is this possible using VBA? Thanks for your help. Regards, Michael . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing the range for averages with out changing the formula. | Excel Worksheet Functions | |||
Searching a date range to output values according to a constant. | Excel Discussion (Misc queries) | |||
Searching a date range to output values according to a constant. | Excel Discussion (Misc queries) | |||
Selecting range in list of range names depending on a cell informa | Excel Discussion (Misc queries) | |||
changing value of a cell by selecting an item from a drop down list | Excel Worksheet Functions |