Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting cell range in a worksheet_change subroutine
I have a worksheet_change subroutine that copies selected cells to another
worksheet as they are entered. This all works fine. My problem occurs when I try and delete data from the original worksheet. If I only delete one cell it works fine and clears the cell in the second sheet. If I select a range and hit delete then it only clears the activecell off the second sheet. Can I use code to force it to do the range. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting cell range in a worksheet_change subroutine
Hi,
Maybe something like this: RngToDelete=Selection.Address Sheets("Sheet2").Range(RngToDelete).ClearContents As always, it is eaisier to make an exact answer, if you posted your current code. Hopes it helps. ... Per On 4 Sep., 03:20, NDBC wrote: I have a worksheet_change subroutine that copies selected cells to another worksheet as they are entered. This all works fine. My problem occurs when I try and delete data from the original worksheet. If I only delete one cell it works fine and clears the cell in the second sheet. If I select a range and hit delete then it only clears the activecell off the second sheet. Can I use code to force it to do the range. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting cell range in a worksheet_change subroutine
Thanks, I can make that work.
My trouble now is I'm getting an error on this line RngToDelete=Selection.Address I'm using excel 2003 and have dim RngToDelete as range. "Per Jessen" wrote: Hi, Maybe something like this: RngToDelete=Selection.Address Sheets("Sheet2").Range(RngToDelete).ClearContents As always, it is eaisier to make an exact answer, if you posted your current code. Hopes it helps. ... Per On 4 Sep., 03:20, NDBC wrote: I have a worksheet_change subroutine that copies selected cells to another worksheet as they are entered. This all works fine. My problem occurs when I try and delete data from the original worksheet. If I only delete one cell it works fine and clears the cell in the second sheet. If I select a range and hit delete then it only clears the activecell off the second sheet. Can I use code to force it to do the range. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting cell range in a worksheet_change subroutine
Just had a thought, maybe error wasn't in that line
RngToDelete = Selection.Address Sheets("a lap").Range(RngToDelete).Offset(0, 3).ClearContents I need to move the selection address over 3 columns to clear in the second sheet. Have I done this wrong and it is just showing up an error on the wrong line "NDBC" wrote: Thanks, I can make that work. My trouble now is I'm getting an error on this line RngToDelete=Selection.Address I'm using excel 2003 and have dim RngToDelete as range. "Per Jessen" wrote: Hi, Maybe something like this: RngToDelete=Selection.Address Sheets("Sheet2").Range(RngToDelete).ClearContents As always, it is eaisier to make an exact answer, if you posted your current code. Hopes it helps. ... Per On 4 Sep., 03:20, NDBC wrote: I have a worksheet_change subroutine that copies selected cells to another worksheet as they are entered. This all works fine. My problem occurs when I try and delete data from the original worksheet. If I only delete one cell it works fine and clears the cell in the second sheet. If I select a range and hit delete then it only clears the activecell off the second sheet. Can I use code to force it to do the range. Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting cell range in a worksheet_change subroutine
Selection.Address is returning a string, so you should use:
Dim RngToDelete As String Regards, Per On 4 Sep., 04:08, NDBC wrote: Thanks, I can make that work. My trouble now is I'm getting an error on this line RngToDelete=Selection.Address I'm using excel 2003 and have dim RngToDelete as range. "Per Jessen" wrote: Hi, Maybe something like this: RngToDelete=Selection.Address Sheets("Sheet2").Range(RngToDelete).ClearContents As always, it is eaisier to make an exact answer, if you posted your current code. Hopes it helps. ... Per On 4 Sep., 03:20, NDBC wrote: I have a worksheet_change subroutine that copies selected cells to another worksheet as they are entered. This all works fine. My problem occurs when I try and delete data from the original worksheet. If I only delete one cell it works fine and clears the cell in the second sheet. If I select a range and hit delete then it only clears the activecell off the second sheet. Can I use code to force it to do the range. Thanks- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error when Target Range is block, not cell in Worksheet_Change Eve | Excel Programming | |||
Disabling worksheet_change when deleting rows . . | Excel Programming | |||
Worksheet Cell Range Name within VB Subroutine | Excel Discussion (Misc queries) | |||
Prevent error when deleting row within Worksheet_Change target ran | Excel Programming | |||
subroutine to get all of the Named range properties | Excel Programming |