VBA code to clearcontents of blank cell

paultje_bos

New Member
Joined
Oct 20, 2014
Messages
39
Sounds weird, but here's the deal. I have a spreadsheet with formulas. Not all formulas have an outcome, in the case the formula leaves the cell blank ("").
Now I need a vba code that looks in colum A to O for all blank cells, selects them and clearcontents.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Code:
columnNumber = 1
Do Until columnNumber >15
    'column 15 is column O
    rowNumber = 1
    'if you have headers, change rowNumber = 1 to rowNumber = 2
    columnLetter = Chr(columnNumber + 64)
    lastRow = Range(columnLetter & Rows.Count).End(xlup).Row
    Do Until rowNumber > lastRow
        If Range(columnLetter & rowNumber).Value = "" Then
            Range(columnLetter & rowNumber).ClearContents
        End If
        rowNumber = rowNumber + 1
    Loop
    columnNumber = columnNumber + 1
Loop
 
Upvote 0
If your data is reasonably large, then you may find this faster.
It would also expand to columns beyond column Z if your needs later change in that regard.


Code:
[color=darkblue]Sub[/color] Clear_Blanks()
  [color=darkblue]Dim[/color] Col [color=darkblue]As[/color] [color=darkblue]Long[/color]

  Application.ScreenUpdating = [color=darkblue]False[/color]
  [color=darkblue]With[/color] ActiveSheet
    .AutoFilterMode = [color=darkblue]False[/color]
    [color=darkblue]With[/color] .UsedRange
      .AutoFilter
      [color=darkblue]For[/color] Col = 1 [color=darkblue]To[/color] .Columns.Count
        .AutoFilter Field:=Col, Criteria1:=""
        .Offset(1).Columns(Col).ClearContents
        .AutoFilter Field:=Col
      [color=darkblue]Next[/color] Col
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    .AutoFilterMode = [color=darkblue]False[/color]
  [color=darkblue]End[/color] [color=darkblue]With[/color]
  Application.ScreenUpdating = [color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Upvote 0
Not the fastest code as is by the dreaded loop, should work ok thou and is robust and easy to use

Seldct the range tou want in this case A - O and run this code

jiuk
Code:
Sub JACKs_Clear_EMPTY()
'// jiuk - written by Jack in the UK
'// 28 Oct 2014
'// for MrExcel.com
'// Feed - /forum/excel-questions/814413-visual-basic-applications-code-clearcontents-blank-cell.html

Dim Target_CELL As Range

Set Target_CELL = Selection

For Each Target_CELL In Selection
     If Len(Target_CELL) = 0 Then Target_CELL.ClearContents
Next Target_CELL

theEND:
Set Target_CELL = Nothing
Exit Sub

End Sub
 
Last edited:
Upvote 0
How can I enter a fixed range in the code? (i.e., B4:E50).

Not the fastest code as is by the dreaded loop, should work ok thou and is robust and easy to use

Seldct the range tou want in this case A - O and run this code

jiuk
Code:
Sub JACKs_Clear_EMPTY()
'// jiuk - written by Jack in the UK
'// 28 Oct 2014
'// for MrExcel.com
'// Feed - /forum/excel-questions/814413-visual-basic-applications-code-clearcontents-blank-cell.html

Dim Target_CELL As Range

Set Target_CELL = Selection

For Each Target_CELL In Selection
     If Len(Target_CELL) = 0 Then Target_CELL.ClearContents
Next Target_CELL

theEND:
Set Target_CELL = Nothing
Exit Sub

End Sub
 
Last edited:
Upvote 0
If your data is reasonably large, then you may find this faster.
It would also expand to columns beyond column Z if your needs later change in that regard.


Code:
[COLOR=darkblue]Sub[/COLOR] Clear_Blanks()
  [COLOR=darkblue]Dim[/COLOR] Col [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]

  Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
  [COLOR=darkblue]With[/COLOR] ActiveSheet
    .AutoFilterMode = [COLOR=darkblue]False[/COLOR]
    [COLOR=darkblue]With[/COLOR] .UsedRange
      .AutoFilter
      [COLOR=darkblue]For[/COLOR] Col = 1 [COLOR=darkblue]To[/COLOR] .Columns.Count
        .AutoFilter Field:=Col, Criteria1:=""
        .Offset(1).Columns(Col).ClearContents
        .AutoFilter Field:=Col
      [COLOR=darkblue]Next[/COLOR] Col
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    .AutoFilterMode = [COLOR=darkblue]False[/COLOR]
  [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
  Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

I notice this code clears the contents of cells if the row is empty. However, I found where there are cells on the same row with contents, the blank cells don't get cleared.
 
Upvote 0
Welcome to the MrExcel board!

I notice this code clears the contents of cells if the row is empty. However, I found where there are cells on the same row with contents, the blank cells don't get cleared.
I'm not experiencing what you describe.
Are you able to describe exactly what you have or provide a small sample & further explanation that demonstrates the issue you mention?

What version of Excel are you using?
 
Upvote 0
Welcome to the MrExcel board!

I'm not experiencing what you describe.
Are you able to describe exactly what you have or provide a small sample & further explanation that demonstrates the issue you mention?

What version of Excel are you using?

I'm using Excel 2016 64 bit. However, it seems the issue is due to the cells which are merged.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top