VB CODE for moving the range of cells down by one row?
 

DEAR ALL

I have managed to get a range marked from active cell to the last row of the
data to colum K.

Need to shift this whole range by one or two rows as required to insert
fresh information. At least pls suggest how to shift the range by one row,
which will also solve my data entry as I have to select the cell and use a
command button to shift the range by one cell each time.

I can even high light how many rows i want to shift and get rows.count and
do, i guess but will first learn to shift by one row first.

NOTE: cannot insert a new row bcos of hard coding in VB for copying the
values and pasting.

Pls help.

BRGDS/CAPTGNVR

VB CODE for moving the range of cells down by one row?
 

hi
to insert 1 row....
ActiveCell.EntireRow.Insert
to insert 2 rows...
Range(ActiveCell, ActiveCell.Offset(1, 0)).EntireRow.Insert
to delete 1 row...
ActiveCell.EntireRow.Delete Shift:=xlUp
to delete 2 rows...
Range(ActiveCell, ActiveCell.Offset(1, 0)).EntireRow.delete Shift:=xlup
to inset 1 cell...
ActiveCell.Insert Shift:=xlDown
to insert 2 cells.....
Range(ActiveCell, ActiveCell.Offset(1, 0)).Insert Shift:=xlDown
to delete 1 cell...
ActiveCell.Delete Shift:=xlUp
to delete 2 cells...
Range(ActiveCell, ActiveCell.Offset(1, 0)).Delete Shift:=xlUp

regards
FSt1
"CAPTGNVR" wrote:

DEAR ALL

I have managed to get a range marked from active cell to the last row of the
data to colum K.

Need to shift this whole range by one or two rows as required to insert
fresh information. At least pls suggest how to shift the range by one row,
which will also solve my data entry as I have to select the cell and use a
command button to shift the range by one cell each time.

I can even high light how many rows i want to shift and get rows.count and
do, i guess but will first learn to shift by one row first.

NOTE: cannot insert a new row bcos of hard coding in VB for copying the
values and pasting.

Pls help.

BRGDS/CAPTGNVR

VB CODE for moving the range of cells down by one row?
 

D/FSt1

Thanks for your response. Tried your steps and sorry as it does not meet my
needs. It inserts a row which should not bcos I have VB code which when
copies a different area and pastes will give wrong results.

For example I highlight a range from A30.K40. Keep cursor on the border and
drag it a row down to shift the whole range by a row or two as required.

This is needed in VB code. As I mentioned in my post I have managed to get
the range A30.K40 selected. What is the code to just pull it down by one or
two rows without inserting a new row.

Eagerly waiting.

with rgds/captgnvr



"FSt1" wrote:

hi
to insert 1 row....
ActiveCell.EntireRow.Insert
to insert 2 rows...
Range(ActiveCell, ActiveCell.Offset(1, 0)).EntireRow.Insert
to delete 1 row...
ActiveCell.EntireRow.Delete Shift:=xlUp
to delete 2 rows...
Range(ActiveCell, ActiveCell.Offset(1, 0)).EntireRow.delete Shift:=xlup
to inset 1 cell...
ActiveCell.Insert Shift:=xlDown
to insert 2 cells.....
Range(ActiveCell, ActiveCell.Offset(1, 0)).Insert Shift:=xlDown
to delete 1 cell...
ActiveCell.Delete Shift:=xlUp
to delete 2 cells...
Range(ActiveCell, ActiveCell.Offset(1, 0)).Delete Shift:=xlUp

regards
FSt1
"CAPTGNVR" wrote:

DEAR ALL

I have managed to get a range marked from active cell to the last row of the
data to colum K.

Need to shift this whole range by one or two rows as required to insert
fresh information. At least pls suggest how to shift the range by one row,
which will also solve my data entry as I have to select the cell and use a
command button to shift the range by one cell each time.

I can even high light how many rows i want to shift and get rows.count and
do, i guess but will first learn to shift by one row first.

NOTE: cannot insert a new row bcos of hard coding in VB for copying the
values and pasting.

Pls help.

BRGDS/CAPTGNVR

VB CODE for moving the range of cells down by one row?
 

hi.
my post covers how to insert/delete rows AND how to insert/delete cells
read down to the bottom of the post for cells

Regards
FSt1

"CAPTGNVR" wrote:

D/FSt1

Thanks for your response. Tried your steps and sorry as it does not meet my
needs. It inserts a row which should not bcos I have VB code which when
copies a different area and pastes will give wrong results.

For example I highlight a range from A30.K40. Keep cursor on the border and
drag it a row down to shift the whole range by a row or two as required.

This is needed in VB code. As I mentioned in my post I have managed to get
the range A30.K40 selected. What is the code to just pull it down by one or
two rows without inserting a new row.

Eagerly waiting.

with rgds/captgnvr



"FSt1" wrote:

hi
to insert 1 row....
ActiveCell.EntireRow.Insert
to insert 2 rows...
Range(ActiveCell, ActiveCell.Offset(1, 0)).EntireRow.Insert
to delete 1 row...
ActiveCell.EntireRow.Delete Shift:=xlUp
to delete 2 rows...
Range(ActiveCell, ActiveCell.Offset(1, 0)).EntireRow.delete Shift:=xlup
to inset 1 cell...
ActiveCell.Insert Shift:=xlDown
to insert 2 cells.....
Range(ActiveCell, ActiveCell.Offset(1, 0)).Insert Shift:=xlDown
to delete 1 cell...
ActiveCell.Delete Shift:=xlUp
to delete 2 cells...
Range(ActiveCell, ActiveCell.Offset(1, 0)).Delete Shift:=xlUp

regards
FSt1
"CAPTGNVR" wrote:

DEAR ALL

I have managed to get a range marked from active cell to the last row of the
data to colum K.

Need to shift this whole range by one or two rows as required to insert
fresh information. At least pls suggest how to shift the range by one row,
which will also solve my data entry as I have to select the cell and use a
command button to shift the range by one cell each time.

I can even high light how many rows i want to shift and get rows.count and
do, i guess but will first learn to shift by one row first.

NOTE: cannot insert a new row bcos of hard coding in VB for copying the
values and pasting.

Pls help.

BRGDS/CAPTGNVR

VB CODE for moving the range of cells down by one row?
 

Hi

Maybe it's "Offset(Row, Column)" you are looking for.

To select the cell below active cell:

ActiveCell.Offset(1,0).Select

Regards,
Per

"CAPTGNVR" skrev i meddelelsen
...
DEAR ALL

I have managed to get a range marked from active cell to the last row of
the
data to colum K.

Need to shift this whole range by one or two rows as required to insert
fresh information. At least pls suggest how to shift the range by one
row,
which will also solve my data entry as I have to select the cell and use a
command button to shift the range by one cell each time.

I can even high light how many rows i want to shift and get rows.count and
do, i guess but will first learn to shift by one row first.

NOTE: cannot insert a new row bcos of hard coding in VB for copying the
values and pasting.

Pls help.

BRGDS/CAPTGNVR

VB CODE for moving the range of cells down by one row?
 

NO MR. PER

What I want is after highlighting the block to drag the whole block by one
row below. Like i mentioned--- once u block, place the curson on the border
and when u see four arrows, u click and drag the block down. This I need it
in VB code.
pls help.

BRGDS/CAPTGNVR

"Per Jessen" wrote:

Hi

Maybe it's "Offset(Row, Column)" you are looking for.

To select the cell below active cell:

ActiveCell.Offset(1,0).Select

Regards,
Per

"CAPTGNVR" skrev i meddelelsen
...
DEAR ALL

I have managed to get a range marked from active cell to the last row of
the
data to colum K.

Need to shift this whole range by one or two rows as required to insert
fresh information. At least pls suggest how to shift the range by one
row,
which will also solve my data entry as I have to select the cell and use a
command button to shift the range by one cell each time.

I can even high light how many rows i want to shift and get rows.count and
do, i guess but will first learn to shift by one row first.

NOTE: cannot insert a new row bcos of hard coding in VB for copying the
values and pasting.

Pls help.

BRGDS/CAPTGNVR

VB CODE for moving the range of cells down by one row?
 

The following one-liner should shift your data down one row...
Put it in your code at the point you need the shift.


ActiveCell.EntireRow.Insert shift:=xlDown


Mark Ivey

"CAPTGNVR" wrote in message
...
DEAR ALL

I have managed to get a range marked from active cell to the last row of
the
data to colum K.

Need to shift this whole range by one or two rows as required to insert
fresh information. At least pls suggest how to shift the range by one
row,
which will also solve my data entry as I have to select the cell and use a
command button to shift the range by one cell each time.

I can even high light how many rows i want to shift and get rows.count and
do, i guess but will first learn to shift by one row first.

NOTE: cannot insert a new row bcos of hard coding in VB for copying the
values and pasting.

Pls help.

BRGDS/CAPTGNVR

VB CODE for moving the range of cells down by one row?
 

Just read your total requirements...

Try the following code...


ActiveCell.EntireRow.Insert shift:=xlDown
Selection.Offset(1, 0).Select


Mark Ivey



"CAPTGNVR" wrote in message
...
NO MR. PER

What I want is after highlighting the block to drag the whole block by one
row below. Like i mentioned--- once u block, place the curson on the
border
and when u see four arrows, u click and drag the block down. This I need
it
in VB code.
pls help.

BRGDS/CAPTGNVR

"Per Jessen" wrote:

Hi

Maybe it's "Offset(Row, Column)" you are looking for.

To select the cell below active cell:

ActiveCell.Offset(1,0).Select

Regards,
Per

"CAPTGNVR" skrev i meddelelsen
...
DEAR ALL

I have managed to get a range marked from active cell to the last row
of
the
data to colum K.

Need to shift this whole range by one or two rows as required to insert
fresh information. At least pls suggest how to shift the range by one
row,
which will also solve my data entry as I have to select the cell and
use a
command button to shift the range by one cell each time.

I can even high light how many rows i want to shift and get rows.count
and
do, i guess but will first learn to shift by one row first.

NOTE: cannot insert a new row bcos of hard coding in VB for copying
the
values and pasting.

Pls help.

BRGDS/CAPTGNVR

VB CODE for moving the range of cells down by one row?
 

And if you needed two rows... try this


ActiveCell.EntireRow.Insert shift:=xlDown
ActiveCell.EntireRow.Insert shift:=xlDown
Selection.Offset(2, 0).Select


Mark Ivey

"CAPTGNVR" wrote in message
...
NO MR. PER

What I want is after highlighting the block to drag the whole block by one
row below. Like i mentioned--- once u block, place the curson on the
border
and when u see four arrows, u click and drag the block down. This I need
it
in VB code.
pls help.

BRGDS/CAPTGNVR

"Per Jessen" wrote:

Hi

Maybe it's "Offset(Row, Column)" you are looking for.

To select the cell below active cell:

ActiveCell.Offset(1,0).Select

Regards,
Per

"CAPTGNVR" skrev i meddelelsen
...
DEAR ALL

I have managed to get a range marked from active cell to the last row
of
the
data to colum K.

Need to shift this whole range by one or two rows as required to insert
fresh information. At least pls suggest how to shift the range by one
row,
which will also solve my data entry as I have to select the cell and
use a
command button to shift the range by one cell each time.

I can even high light how many rows i want to shift and get rows.count
and
do, i guess but will first learn to shift by one row first.

NOTE: cannot insert a new row bcos of hard coding in VB for copying
the
values and pasting.

Pls help.

BRGDS/CAPTGNVR

VB CODE for moving the range of cells down by one row?
 

GD EVE MARK, PER AND FST1

This given code ActiveCell.EntireRow.Insert shift:=xlDown
wont work for me bcos I want to only shift the range A30.K40, to A31. So I
dont want the activecell.entirerow.

So pleas suggest a solution to drag only the highlighted block A30.K40.
Once again what I actually need is the range is highlighted, point cursor on
the border and when u see four arrow click the mouse drag it one row below
and release.

Mr. Mark since u hv seen this program of mind and send a modified code, this
is for blocking a list of names, pull it down by a row without inserting
rows, to enter new crew names.

brgds/captgnvr


"Mark Ivey" wrote:

The following one-liner should shift your data down one row...
Put it in your code at the point you need the shift.


ActiveCell.EntireRow.Insert shift:=xlDown


Mark Ivey

"CAPTGNVR" wrote in message
...
DEAR ALL

I have managed to get a range marked from active cell to the last row of
the
data to colum K.

Need to shift this whole range by one or two rows as required to insert
fresh information. At least pls suggest how to shift the range by one
row,
which will also solve my data entry as I have to select the cell and use a
command button to shift the range by one cell each time.

I can even high light how many rows i want to shift and get rows.count and
do, i guess but will first learn to shift by one row first.

NOTE: cannot insert a new row bcos of hard coding in VB for copying the
values and pasting.

Pls help.

BRGDS/CAPTGNVR

VB CODE for moving the range of cells down by one row?
 

Hi again

Once your range is selected use this code to shift it one row down:

Selection.Cut Selection.Offset(1, 0)

Best regards,
Per

"CAPTGNVR" skrev i meddelelsen
...
GD EVE MARK, PER AND FST1

This given code ActiveCell.EntireRow.Insert shift:=xlDown
wont work for me bcos I want to only shift the range A30.K40, to A31. So
I
dont want the activecell.entirerow.

So pleas suggest a solution to drag only the highlighted block A30.K40.
Once again what I actually need is the range is highlighted, point cursor
on
the border and when u see four arrow click the mouse drag it one row below
and release.

Mr. Mark since u hv seen this program of mind and send a modified code,
this
is for blocking a list of names, pull it down by a row without inserting
rows, to enter new crew names.

brgds/captgnvr


"Mark Ivey" wrote:

The following one-liner should shift your data down one row...
Put it in your code at the point you need the shift.


ActiveCell.EntireRow.Insert shift:=xlDown


Mark Ivey

"CAPTGNVR" wrote in message
...
DEAR ALL

I have managed to get a range marked from active cell to the last row
of
the
data to colum K.

Need to shift this whole range by one or two rows as required to insert
fresh information. At least pls suggest how to shift the range by one
row,
which will also solve my data entry as I have to select the cell and
use a
command button to shift the range by one cell each time.

I can even high light how many rows i want to shift and get rows.count
and
do, i guess but will first learn to shift by one row first.

NOTE: cannot insert a new row bcos of hard coding in VB for copying
the
values and pasting.

Pls help.

BRGDS/CAPTGNVR

VB CODE for moving the range of cells down by one row?
 

D/PER

BINGO. Selection.Cut Selection.Offset(1, 0)
worked the way I need. Thanks a lot for the quick responses. My lucky day
today to get it solved so quickly.

Mr. Mark lots of typo and so read as follows (Mr. Mark since u hv seen this
program of mine and sent me a modified code.......)

Anyhow, Mr. Per lines solved my need and will consider this threat closed.

wrgds/captgnvr

"Per Jessen" wrote:

Hi again

Once your range is selected use this code to shift it one row down:

Selection.Cut Selection.Offset(1, 0)

Best regards,
Per

"CAPTGNVR" skrev i meddelelsen
...
GD EVE MARK, PER AND FST1

This given code ActiveCell.EntireRow.Insert shift:=xlDown
wont work for me bcos I want to only shift the range A30.K40, to A31. So
I
dont want the activecell.entirerow.

So pleas suggest a solution to drag only the highlighted block A30.K40.
Once again what I actually need is the range is highlighted, point cursor
on
the border and when u see four arrow click the mouse drag it one row below
and release.

Mr. Mark since u hv seen this program of mind and send a modified code,
this
is for blocking a list of names, pull it down by a row without inserting
rows, to enter new crew names.

brgds/captgnvr


"Mark Ivey" wrote:

The following one-liner should shift your data down one row...
Put it in your code at the point you need the shift.


ActiveCell.EntireRow.Insert shift:=xlDown


Mark Ivey

"CAPTGNVR" wrote in message
...
DEAR ALL

I have managed to get a range marked from active cell to the last row
of
the
data to colum K.

Need to shift this whole range by one or two rows as required to insert
fresh information. At least pls suggest how to shift the range by one
row,
which will also solve my data entry as I have to select the cell and
use a
command button to shift the range by one cell each time.

I can even high light how many rows i want to shift and get rows.count
and
do, i guess but will first learn to shift by one row first.

NOTE: cannot insert a new row bcos of hard coding in VB for copying
the
values and pasting.

Pls help.

BRGDS/CAPTGNVR

VB CODE for moving the range of cells down by one row?
 

And for 2 rows down:

Selection.Cut Selection.Offset(1, 0)
Selection.Offset(1, 0).Select
Selection.Cut Selection.Offset(1, 0)
Selection.Offset(1, 0).Select

Mike F
"Per Jessen" wrote in message
...
Hi again

Once your range is selected use this code to shift it one row down:

Selection.Cut Selection.Offset(1, 0)

Best regards,
Per

"CAPTGNVR" skrev i meddelelsen
...
GD EVE MARK, PER AND FST1

This given code ActiveCell.EntireRow.Insert shift:=xlDown
wont work for me bcos I want to only shift the range A30.K40, to A31. So
I
dont want the activecell.entirerow.

So pleas suggest a solution to drag only the highlighted block A30.K40.
Once again what I actually need is the range is highlighted, point cursor
on
the border and when u see four arrow click the mouse drag it one row
below
and release.

Mr. Mark since u hv seen this program of mind and send a modified code,
this
is for blocking a list of names, pull it down by a row without inserting
rows, to enter new crew names.

brgds/captgnvr


"Mark Ivey" wrote:

The following one-liner should shift your data down one row...
Put it in your code at the point you need the shift.


ActiveCell.EntireRow.Insert shift:=xlDown


Mark Ivey

"CAPTGNVR" wrote in message
...
DEAR ALL

I have managed to get a range marked from active cell to the last row
of
the
data to colum K.

Need to shift this whole range by one or two rows as required to
insert
fresh information. At least pls suggest how to shift the range by one
row,
which will also solve my data entry as I have to select the cell and
use a
command button to shift the range by one cell each time.

I can even high light how many rows i want to shift and get rows.count
and
do, i guess but will first learn to shift by one row first.

NOTE: cannot insert a new row bcos of hard coding in VB for copying
the
values and pasting.

Pls help.

BRGDS/CAPTGNVR

VB CODE for moving the range of cells down by one row?
 

or simpler yet:

Selection.Cut Selection.Offset(2, 0)
Selection.Offset(2, 0).Select

Mike F
"Mike Fogleman" wrote in message
...
And for 2 rows down:

Selection.Cut Selection.Offset(1, 0)
Selection.Offset(1, 0).Select
Selection.Cut Selection.Offset(1, 0)
Selection.Offset(1, 0).Select

Mike F
"Per Jessen" wrote in message
...
Hi again

Once your range is selected use this code to shift it one row down:

Selection.Cut Selection.Offset(1, 0)

Best regards,
Per

"CAPTGNVR" skrev i meddelelsen
...
GD EVE MARK, PER AND FST1

This given code ActiveCell.EntireRow.Insert shift:=xlDown
wont work for me bcos I want to only shift the range A30.K40, to A31.
So I
dont want the activecell.entirerow.

So pleas suggest a solution to drag only the highlighted block A30.K40.
Once again what I actually need is the range is highlighted, point
cursor on
the border and when u see four arrow click the mouse drag it one row
below
and release.

Mr. Mark since u hv seen this program of mind and send a modified code,
this
is for blocking a list of names, pull it down by a row without inserting
rows, to enter new crew names.

brgds/captgnvr


"Mark Ivey" wrote:

The following one-liner should shift your data down one row...
Put it in your code at the point you need the shift.


ActiveCell.EntireRow.Insert shift:=xlDown


Mark Ivey

"CAPTGNVR" wrote in message
...
DEAR ALL

I have managed to get a range marked from active cell to the last row
of
the
data to colum K.

Need to shift this whole range by one or two rows as required to
insert
fresh information. At least pls suggest how to shift the range by
one
row,
which will also solve my data entry as I have to select the cell and
use a
command button to shift the range by one cell each time.

I can even high light how many rows i want to shift and get
rows.count and
do, i guess but will first learn to shift by one row first.

NOTE: cannot insert a new row bcos of hard coding in VB for copying
the
values and pasting.

Pls help.

BRGDS/CAPTGNVR