Macro to check and delete content of cell

[kent97]

Dear all,

I want to write a macro which will run in relative cell references

1. The macro starts at cell A1, say, activated by ctrl"k"
2. After the macro starts, it checks the content of cell C1
3. If C1 is a space, delete the cell content, then goto C2.
4. If C1 is not a space, skip to C2
5. Repeat step 3 & 4 up to C100
6. After all the 100 cells are checked and deleted if necessary, the macro stops at cell K1.

Thank you for your assistance.

[stnkynts]

Please Login or Register  to view this content.

[kent97]

Please Login or Register  to view this content.

Thank you stnkynts.

Your macro is written in absolute cell reference mode, how about in relative cell reference mode please?

[jindon]

Try

Please Login or Register  to view this content.

[Crooza]

If you want it to run relative and I assume by this you mean run for rows 1 to 100 but 2 columns to the right (based on your description above of starting in A1 and running in column C) then try this. It uses Stnkynts macro but modifies to run in the column two columns to the right and starting at the same rown number as the present activecell

Please Login or Register  to view this content.

[kent97]

[QUOTE=Crooza;4012598]If you want it to run relative and I assume by this you mean run for rows 1 to 100 but 2 columns to the right (based on your description above of starting in A1 and running in column C) then try this. It uses Stnkynts macro but modifies to run in the column two columns to the right and starting at the same rown number as the present activecell

[CODE]Sub kent()
Dim c As Range


Dear Crooza,

Thank you for your suggestion.

1. Actually I need to clear the cell content if the cell content a space, but not only trim the space. As the space is resulted by a formula set in the cell, and this formula should be cleared as well.

2. Your Macro always stops at K1, I want it stops 10 columns to the right, i.e. stop at K1 when start at A1, stop at L1 when start at B1.

Any new idea please help again.

Kent

[jindon]

Please Login or Register  to view this content.

[Crooza]

This should address point 2 above. In regard to point 1 the first code should have addressed a space regardless of whether it was the result of a formula or simply text. Maybe post your worksheet up so we can see what isn't working

Please Login or Register  to view this content.

[kent97]

Dear Crooza and jindon,

I got it by using your suggestion "Range(ActiveCell.Offset(0, 10), ActiveCell.Offset(0, 10)).Select" to solve the stop position.
I have also used "If cell.Value = " " Then cell.ClearContents" to clear the cell content.

Thanks a lot.
Kent

[Crooza]

Great. Glad it worked. You should mark the thread as solved now

[kent97]

Great. Glad it worked. You should mark the thread as solved now

Marked as told.