Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move selected range to left
I have a macro that selects a range of cells in a column that contains data.
I want to then offset that selection to the next column to the left, that column has no data. I tried: Range(ActiveCell, ActiveCell.End(xlDown)).Select ActiiveColumn.Offset(-1, 0).Select The first command line selects the proper data range. The second line is wrong. Also, without using cell references, how do I select the topmost cell in a column or leftmost cell in a row from a random location cell? Is there an online reference for VBA commands? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move selected range to left
how about
activecell.offset(,-1).select or Range(ActiveCell, ActiveCell.End(xlDown)).offset(,-1).Select However, you need NOT select to do things. Post all of your code for comments. -- Don Guillett Microsoft MVP Excel SalesAid Software "Gene Augustin" wrote in message m... I have a macro that selects a range of cells in a column that contains data. I want to then offset that selection to the next column to the left, that column has no data. I tried: Range(ActiveCell, ActiveCell.End(xlDown)).Select ActiiveColumn.Offset(-1, 0).Select The first command line selects the proper data range. The second line is wrong. Also, without using cell references, how do I select the topmost cell in a column or leftmost cell in a row from a random location cell? Is there an online reference for VBA commands? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move selected range to left
Don, neither works.
The first generates "expected number" The second "expected =" Here's the entire macro: Sub AddItemNumber() ' ' AddItemNumber Macro ' Macro recorded 2/13/2009 by Gene Augustin After Paste special data, add new column with item number, Select Range, sort up on data,delete blank data rows, select new range, sort up on Item number. ' Selection.EntireColumn.Insert ActiveCell.FormulaR1C1 = "Item" ActiveCell.Offset(0, 1).Select Range(ActiveCell, ActiveCell.End(xlDown)).Select 'I have selected the proper range in the adjacent column, I want to fill in 'seriaized numbers beginning with ³1²in the next column to the left over 'this range. The next 3 lines put the range starting number ³1² in the proper cell, but it deselects the range ActiveCell.Offset(0, -1).Select ActiveCell.Offset(1, 0).Select ActiveCell.Formula = "1" Since no range is selected, the next command does nothing. Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _ Step:=1, Trend:=False End Sub This is the first part of a very long macro process Iım creating. On 2/13/09 10:35 AM, in article , "Don Guillett" wrote: how about activecell.offset(,-1).select or Range(ActiveCell, ActiveCell.End(xlDown)).offset(,-1).Select However, you need NOT select to do things. Post all of your code for comments. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move selected range to left
Move selected range to leftDoes this code do what you are trying to do...
Sub AddItemNumber() Dim LastRow As Long LastRow = ActiveCell.Offset(, 1).End(xlDown).Offset(, -1).Row ActiveCell.EntireColumn.Insert With ActiveCell .Value = "Item" .Offset(1).Value = 1 .Offset(1).Resize(LastRow - .Row).DataSeries Rowcol:=xlColumns, _ Type:=xlLinear, Date:=xlDay, Step:=1, Trend:=False End With End Sub -- Rick (MVP - Excel) "Gene Augustin" wrote in message m... Don, neither works. The first generates "expected number" The second "expected =" Here's the entire macro: ------------------------------------------------------------------------------ Sub AddItemNumber() ' ' AddItemNumber Macro ' Macro recorded 2/13/2009 by Gene Augustin 'After Paste special data, add new column with item number, 'Select Range, sort up on data,delete blank data rows, select new range, 'sort up on Item number. ' Selection.EntireColumn.Insert ActiveCell.FormulaR1C1 = "Item" ActiveCell.Offset(0, 1).Select Range(ActiveCell, ActiveCell.End(xlDown)).Select 'I have selected the proper range in the adjacent column, I want to fill in 'seriaized numbers beginning with "1"in the next column to the left over 'this range. 'The next 3 lines put the range starting number "1" in the proper cell, 'but it deselects the range ActiveCell.Offset(0, -1).Select ActiveCell.Offset(1, 0).Select ActiveCell.Formula = "1" 'Since no range is selected, the next command does nothing. Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _ Step:=1, Trend:=False End Sub ------------------------------------------------------------------------------ This is the first part of a very long macro process I'm creating. On 2/13/09 10:35 AM, in article , "Don Guillett" wrote: how about activecell.offset(,-1).select or Range(ActiveCell, ActiveCell.End(xlDown)).offset(,-1).Select However, you need NOT select to do things. Post all of your code for comments. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move selected range to left
This almost does it.It fills in a series in the proper column, but goes the
entire length of the column instead of just the range of cells. For test, create a workbook with R1C1=²DATA² Then randomly put data in 10 cells in the column with a few of the interior cells blank and run the macro. It should produce a new column with name ³item² in cell r1c1 and serial numbers in r2:r:11 It produces numbers all the way down. On 2/13/09 3:01 PM, in article , "Rick Rothstein" wrote: Does this code do what you are trying to do... Sub AddItemNumber() Dim LastRow As Long LastRow = ActiveCell.Offset(, 1).End(xlDown).Offset(, -1).Row ActiveCell.EntireColumn.Insert With ActiveCell .Value = "Item" .Offset(1).Value = 1 .Offset(1).Resize(LastRow - .Row).DataSeries Rowcol:=xlColumns, _ Type:=xlLinear, Date:=xlDay, Step:=1, Trend:=False End With End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move selected range to left
Move selected range to leftHow about this code then?
Sub AddItemNumber() Dim LastRow As Long LastRow = ActiveCell.End(xlDown).Offset(, -1).Row ActiveCell.EntireColumn.Insert With ActiveCell .Value = "Item" .Offset(1).Value = 1 .Offset(1).Resize(LastRow - .Row).DataSeries Rowcol:=xlColumns, _ Type:=xlLinear, Date:=xlDay, Step:=1, Trend:=False End With End Sub -- Rick (MVP - Excel) "Gene Augustin" wrote in message m... This almost does it.It fills in a series in the proper column, but goes the entire length of the column instead of just the range of cells. For test, create a workbook with R1C1="DATA" Then randomly put data in 10 cells in the column with a few of the interior cells blank and run the macro. It should produce a new column with name "item" in cell r1c1 and serial numbers in r2:r:11 It produces numbers all the way down. On 2/13/09 3:01 PM, in article , "Rick Rothstein" wrote: Does this code do what you are trying to do... Sub AddItemNumber() Dim LastRow As Long LastRow = ActiveCell.Offset(, 1).End(xlDown).Offset(, -1).Row ActiveCell.EntireColumn.Insert With ActiveCell .Value = "Item" .Offset(1).Value = 1 .Offset(1).Resize(LastRow - .Row).DataSeries Rowcol:=xlColumns, _ Type:=xlLinear, Date:=xlDay, Step:=1, Trend:=False End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
More than one cell is selected when I left click. Why? | New Users to Excel | |||
how to make left side stay still and right side can move left to r | Excel Discussion (Misc queries) | |||
Move contents of selected cell down one, and to the left one cell? | Excel Programming | |||
Getting Range to move 1 column left and resize by 1 | Excel Programming | |||
selected cell - top left | Excel Discussion (Misc queries) |