How can I clear Active Row using VBA?

[jdcortez]

Hi everyone,

I have a macro that clears the contents of the selected range in a worksheet, then it shifts the bottom cells up. I am wondering if there is a way to avoid selecting the range and clearing the ActiveRow up to Column M. Here is my macro:

Please Login or Register  to view this content.

[Roel Jongman]

This code will delete the contents of the col A thru M on the row of the activeCell (which is also the active row)

Please Login or Register  to view this content.

[jdcortez]

This code will delete the contents of the col A thru M on the row of the activeCell (which is also the active row)

Please Login or Register  to view this content.

Unfortunately I cannot use delete as it will cause an error on my referenced cells. Is there any way to make your solution work with "Clear Contents"

[Roel Jongman]

Ah, ok,

see if this code works then.. still only work on the activecell / active row but always A:M is cleared
and all remaining rows are moved up 1 row

Please Login or Register  to view this content.

__________________
forumguideline:
2. Please don't quote whole posts -- it's just clutter.*

[jdcortez]

Spoke too soon, its actually is causing an error on the referenced cell Side question, what if i wanted to ClearContents from C:M, which values would i change?

[Roel Jongman]

there is a few changes needed so here is changed code with highlighted changes

Please Login or Register  to view this content.

the 1's changed to 3 to reflect col C instead of A
the resize only needs to expand 11 col from C to m instead of 13 from A to M.

tip: if you press F1 in VBA editor while on a command f.e. Cells or Resize it will take you to the help and explain what it does. and what references rows and columns

it might de down to me using the Cut and paste function that affects your references
but because your example does only have constant values and no references pointing to other cells I cannot test if changing to copy paste code will work better.

[jdcortez]

i uploaded another workbook that references cell $A$2

[Roel Jongman]

Ok so the reference is outside of the data area that is being cleared and moved.
IF you keep the values in column A and B does that then not affect the outcome? I mean col C: M will move 1 row up but A and B are still the same, also there will be values in A and B column at the bottom without any values in C:M. that amount of rows like that will grow depending on how often you delete the current row.

So based on the new file. I would stick to copying A:M and use copy/paste
that way you are sure all data of each line stays as it was before the delete of the active row.

Please Login or Register  to view this content.

[mohan.r1980]

try

Please Login or Register  to view this content.