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

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

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

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

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