Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a macro to delete cells from any row
Good morning,
I have set-up the following macro to delete certain cells of a row. Sub test() ' ' test Macro ' ' Keyboard Shortcut: Ctrl+t ' Range("A19:A19").Select ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 7 Range("A19:I19,L19,N19:U19").Select Range("A19").Activate Selection.ClearContents End Sub It deletes the cells that I need it to, however it always deletes the cells in row 19 (Since that is where I recorded the macro). What can I put in the code to get the macro to delete the cells from whatever row is active (i.e. if I click on cell "A4" have the macro delete the cells in row 4, or if cell "A250" is clicked it deletes the cells in row 250 etc...)? Thanks for any help that can be provided. Chad |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a macro to delete cells from any row
Try the below
lngRow = ActiveCell.Row Range("A" & lngRow & ":I" & lngRow).ClearContents Range("N" & lngRow & ":U" & lngRow).ClearContents Range("L" & lngRow).ClearContents If this post helps click Yes --------------- Jacob Skaria "CYaYa" wrote: Good morning, I have set-up the following macro to delete certain cells of a row. Sub test() ' ' test Macro ' ' Keyboard Shortcut: Ctrl+t ' Range("A19:A19").Select ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 7 Range("A19:I19,L19,N19:U19").Select Range("A19").Activate Selection.ClearContents End Sub It deletes the cells that I need it to, however it always deletes the cells in row 19 (Since that is where I recorded the macro). What can I put in the code to get the macro to delete the cells from whatever row is active (i.e. if I click on cell "A4" have the macro delete the cells in row 4, or if cell "A250" is clicked it deletes the cells in row 250 etc...)? Thanks for any help that can be provided. Chad |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a macro to delete cells from any row
Hello Chad,
This should do it: Sub test() TargetRow = ActiveCell.Row Range("A" & TargetRow & ":I" & TargetRow & ",L" & TargetRow _ & " ,N" & TargetRow & ":U" & TargetRow).ClearContents End Sub Regards, Per On 22 Jul., 15:11, CYaYa wrote: Good morning, I have set-up the following macro to delete certain cells of a row. Sub test() ' ' test Macro ' ' Keyboard Shortcut: Ctrl+t ' * * Range("A19:A19").Select * * ActiveWindow.ScrollColumn = 3 * * ActiveWindow.ScrollColumn = 4 * * ActiveWindow.ScrollColumn = 5 * * ActiveWindow.ScrollColumn = 6 * * ActiveWindow.ScrollColumn = 7 * * Range("A19:I19,L19,N19:U19").Select * * Range("A19").Activate * * Selection.ClearContents End Sub It deletes the cells that I need it to, however it always deletes the cells in row 19 (Since that is where I recorded the macro). What can I put in the code to get the macro to delete the cells from whatever row is active (i.e. if I click on cell "A4" have the macro delete the cells in row 4, or if cell "A250" is clicked it deletes the cells in row 250 etc...)? Thanks for any help that can be provided. Chad |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a macro to delete cells from any row
chad -
this will do what you say you want: sub test() ActiveCell.EntireRow.Delete end sub however - note that while you discuss deleting cells, what you are actually doing in your macro is clearing the contents. the macro above actually deletes the row, all the way from column A to column ....... (whatever the last column is in your version). if you want to just delete the cells, then you'd have to do something like this: sub test() ActiveCell.Offset(0, 2).Delete Shift:=xlUp end sub hope it helps. :) susan On Jul 22, 9:11*am, CYaYa wrote: Good morning, I have set-up the following macro to delete certain cells of a row. Sub test() ' ' test Macro ' ' Keyboard Shortcut: Ctrl+t ' * * Range("A19:A19").Select * * ActiveWindow.ScrollColumn = 3 * * ActiveWindow.ScrollColumn = 4 * * ActiveWindow.ScrollColumn = 5 * * ActiveWindow.ScrollColumn = 6 * * ActiveWindow.ScrollColumn = 7 * * Range("A19:I19,L19,N19:U19").Select * * Range("A19").Activate * * Selection.ClearContents End Sub It deletes the cells that I need it to, however it always deletes the cells in row 19 (Since that is where I recorded the macro). What can I put in the code to get the macro to delete the cells from whatever row is active (i.e. if I click on cell "A4" have the macro delete the cells in row 4, or if cell "A250" is clicked it deletes the cells in row 250 etc...)? Thanks for any help that can be provided. Chad |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a macro to delete cells from any row
Jacob, Per, and Susan thanks for the input! The macro works exactly how I
need it to now. Susan all I needed was to clear the cells not delete them, but I can use the macro you provided for another sheet. Thanks again to all of you. Chad "Susan" wrote: chad - this will do what you say you want: sub test() ActiveCell.EntireRow.Delete end sub however - note that while you discuss deleting cells, what you are actually doing in your macro is clearing the contents. the macro above actually deletes the row, all the way from column A to column ....... (whatever the last column is in your version). if you want to just delete the cells, then you'd have to do something like this: sub test() ActiveCell.Offset(0, 2).Delete Shift:=xlUp end sub hope it helps. :) susan On Jul 22, 9:11 am, CYaYa wrote: Good morning, I have set-up the following macro to delete certain cells of a row. Sub test() ' ' test Macro ' ' Keyboard Shortcut: Ctrl+t ' Range("A19:A19").Select ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 7 Range("A19:I19,L19,N19:U19").Select Range("A19").Activate Selection.ClearContents End Sub It deletes the cells that I need it to, however it always deletes the cells in row 19 (Since that is where I recorded the macro). What can I put in the code to get the macro to delete the cells from whatever row is active (i.e. if I click on cell "A4" have the macro delete the cells in row 4, or if cell "A250" is clicked it deletes the cells in row 250 etc...)? Thanks for any help that can be provided. Chad |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to delete certain cells | Excel Programming | |||
macro to select cells containing specific text and delete all cells but these | Excel Programming | |||
Macro to delete row with protected cells | Excel Programming | |||
Macro to delete all blank cells | Excel Programming | |||
How do I set up a macro to automatically delete certain cells? | Excel Programming |