Move selected range to left

[Gene Augustin]

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?

 

[Don Guillett]

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.

 

[Gene Augustin]

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.

 

[Rick Rothstein]

Re: 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)


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

 

[Gene Augustin]

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.

 

[Rick Rothstein]

Re: 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)


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.




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