Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select & Move Range
I need a macro to select from the first non-blank cell (containing data) to
the last non-blank cell in a row containing data. There are blank cells between the first and last cell containing data. I then want to move that selected range (not the data) to another row. Something like: FirstCell = first non-blank cell LastCell = last non blank cell Range = (firstcell,lastcell) Range.Offset(-1,0) Gene |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select & Move Range
One way. However, selections are seldom desirable. Post all of your code.
Sub findfirstlastcell() mc = 4 '"d" fr = Cells(2, mc).End(xlDown).Row lr = Cells(Rows.Count, mc).End(xlUp).Row Range(Cells(fr, mc), Cells(lr, mc)).Offset(, -1).Select 'or 'Range(Cells(fr, mc-1), Cells(lr, mc-1)).Select End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Gene Augustin" wrote in message m... I need a macro to select from the first non-blank cell (containing data) to the last non-blank cell in a row containing data. There are blank cells between the first and last cell containing data. I then want to move that selected range (not the data) to another row. Something like: FirstCell = first non-blank cell LastCell = last non blank cell Range = (firstcell,lastcell) Range.Offset(-1,0) Gene |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select & Move Range
At first, I got error messages. I added Dim statements for the variables and
I runs, but doesn't give correct results. Here are the contents of A1:A12 CELL CONTENTS B1 Data B2 1 B3 B4 2 B5 B6 B7 3 B8 4 B9 5 B10 6 B11 7 B12 8 I select the cell below The cell containing "Data" and run the macro. I want the macro to select the 11 cell range B2:B12 and move the selection range to A2:A12. The macro produces a selection of 9 cells beginning in row 4 of the adjacent column. It should produce the selection the 11 cell range A2:A12. Both versions of the Range(Cells line produce the same results. I mis-stated the problem in my original request. I want the range from the second cell in the column to the last non-blank cell in the colum. Here's your modified code with the DIM statements added: Sub findfirstlastcell() Dim mc As Long Dim fr As Long Dim lr As Long mc = 2 ' "d" fr = Cells(mc, 2).End(xlDown).Row lr = Cells(Rows.Count, mc).End(xlUp).Row Range(Cells(fr, mc), Cells(lr, mc)).Offset(, -1).Select 'or 'Range(Cells(fr, mc-1), Cells(lr, mc-1)).Select End Sub On 2/15/09 10:17 AM, in article , "Don Guillett" wrote: One way. However, selections are seldom desirable. Post all of your code. Sub findfirstlastcell() mc = 4 '"d" fr = Cells(2, mc).End(xlDown).Row lr = Cells(Rows.Count, mc).End(xlUp).Row Range(Cells(fr, mc), Cells(lr, mc)).Offset(, -1).Select 'or 'Range(Cells(fr, mc-1), Cells(lr, mc-1)).Select End Sub |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select & Move Range
On 2/15/09 12:03 PM, in article
, "Gene Augustin" wrote: At first, I got error messages. I added Dim statements for the variables and I runs, but doesn't give correct results. Here are the contents of A1:A12 CELL CONTENTS B1 Data B2 1 B3 B4 2 B5 B6 B7 3 B8 4 B9 5 B10 6 B11 7 B12 8 I select the cell below The cell containing "Data" and run the macro. I want the macro to select the 11 cell range B2:B12 and move the selection range to A2:A12. The macro produces a selection of 9 cells beginning in row 4 of the adjacent column. It should produce the selection the 11 cell range A2:A12. Both versions of the Range(Cells line produce the same results. I mis-stated the problem in my original request. I want the range from the second cell in the column to the last non-blank cell in the colum. Here's your modified code with the DIM statements added: Sub findfirstlastcell() Dim mc As Long Dim fr As Long Dim lr As Long mc = 2 ' "d" fr = Cells(mc, 2).End(xlDown).Row lr = Cells(Rows.Count, mc).End(xlUp).Row Range(Cells(fr, mc), Cells(lr, mc)).Offset(, -1).Select 'or 'Range(Cells(fr, mc-1), Cells(lr, mc-1)).Select End Sub Problem solved. I redefined fr and it works now. fr = Cells(2, 2).Row I donšt understand what is happening with statement Cells(2,2) Can you explain? On 2/15/09 10:17 AM, in article , "Don Guillett" wrote: One way. However, selections are seldom desirable. Post all of your code. Sub findfirstlastcell() mc = 4 '"d" fr = Cells(2, mc).End(xlDown).Row lr = Cells(Rows.Count, mc).End(xlUp).Row Range(Cells(fr, mc), Cells(lr, mc)).Offset(, -1).Select 'or 'Range(Cells(fr, mc-1), Cells(lr, mc-1)).Select End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I can't select, move or resize an image in Excel?? | Excel Discussion (Misc queries) | |||
Select and Move Cursor Shapes | New Users to Excel | |||
Cannot select single cell. Mouse move automatically creates range. | Excel Discussion (Misc queries) | |||
How do I select all the even rows in a worksheet and move them. | Excel Discussion (Misc queries) | |||
Move select data to another worksheet | Excel Discussion (Misc queries) |