Search cell content and delete if blank

[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 the C1 is blank, delete the cell content.
4. If C1 is not blank, 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.

 

[Comfy]

Something like this could work:

Sub ClearBlanksFromA()
Dim rng As Range
Dim rng2 As Range


Set rng = Range("C1:C100")
Set rng2 = rng.SpecialCells(xlCellTypeBlanks).Offset(, -2)
rng2.ClearContents


End Sub

Although it's not clear which column/row you wish to evaluate for blanks as you want to start in A1 and stop at K1??

 

[Kent97]

Thank you Comfy.

The macro starts at A1, clear column C, then stop at K1.

If the macro starts again, it will starts from K1, clear column M, the stop at U1.

And so on....

 

[Comfy]

2. After the macro starts, it checks the content of cell C1
3. If the C1 is blank, delete the cell content.

At first assumed that this was a typo

The macro starts at A1, clear column C, then stop at K1.

But this backs up your request.

You wish to check Column C for blank cells and delete the content of those blank cells?

 

[Kent97]

Dear Comfy,

Yes, some cells that only contain formula which look like blank. I want to delete the cell content so that the formula it contains is deleted.

 

[Comfy]

This will remove the formulas where the results are blank.

Sub deleteBlanks()
Dim i As Long


For i = 1 To 100
If Cells(i, 3).Value = "" Then Cells(i, 3).Value = ""
Next i
End Sub

However, I'm still not clear on how you wish this work in relation to other columns.

If you select Column A do you want this to run on the column +2 to the right (Column C)

If you select Column B what do you expect to happen?

If you select Column C what do you expect to happen?

 

[Kent97]

For example, if cell A1 is selected when the macro starts, cell C1 will be checked and deleted if the cell is blank, and up to C100. The macro will stop at K1.

Now if cell B1 is selected when the macro starts, cell D1 to D100 will be processed, and the macro will stop at L1.

This is relative cell reference instead of absolute cell reference.

 

[Kent97]

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit;">Sub Deletions()
Dim Rng As Range, cell As Range

'Delete cells with Value set

Set Rng = Range("C:C")
For Each cell In Rng
If cell.Value = " " Then cell.ClearContents
Next cell

End Sub


As above, I already know the macro in absolute cell reference but want to rewrite it in relative cell reference.</code>

 

[Comfy]

Sub deleteBlanks()
Dim i As Long
Dim Col As Long

Col = Activecell.column + 2




For i = 1 To 100
If Cells(i, Col).Value = "" Then Cells(i, Col).Value = ""
Next i
End Sub

 

[Kent97]

Sub deleteBlanks()
Dim i As Long
Dim Col As Long

Col = Activecell.column + 2




For i = 1 To 100
If Cells(i, Col).Value = "" Then Cells(i, Col).Value = ""
Next i
End Sub

Dear Comfy,

You macro works.
Lastly, I want the macro stop at row 1 and 8 column to the right of the present column.
How can I do that.

Thank you again