Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving text around cells without moving boarder lines | Excel Discussion (Misc queries) | |||
CODE 4 MOVING WITHIN A RANGE-TAB | Excel Discussion (Misc queries) | |||
Arrow Keys Moving Window Frame instead of Moving Between Cells | Excel Discussion (Misc queries) | |||
VBA code for averaging a range of cells | Excel Programming | |||
Moving between a cell that is selected to include a range of cells in that row | Excel Programming |