VBA Code Delete Cell Content if met criteria " "

Hello,

Could someone help me to a VBA code tot delete cell contents within a range when a specific criteria is met.

Range = A11:L136
Criteria = " "

I think I have to explain, that the " " is the outcome of an If formula. Excel sees the " " as an infinte number and text as well. In Cell A11 is a formula with the outcome " " I have already set up an VBA code to copy and paste values (skip blanks) in cell A11. So the content of cell A11 is no longer a formlua but simply " ". By the way when I stand in cell A11 you will see nothing in the formula bar. Excel shows nothing, but when you use ISTEXT or =A11>1E+100 on cell A11 the outcome of the formula is TRUE.

I hope someone can help me with this.

I'd suggest uploading your workbook so we can see what is in the cells. Click go advanced and then attachments to upload your workbook. Get rid of any sensitive data and just put a few lines as an example.

Hello ragulduy.

I have uploaded the file. Sheet 2 needs to be exported to an other workbook in order to be able to send it by email without al the other information in the workbook. This is already covered with a VBA code in Module 1. In the same module I have also added a code to eliminate the blank rows in sheet 2. This works great except for the blanks with the “ ” content which applies to all the blank cells.

In below code I want to add a code (in the bold marked field) which delete the “ ” content in the blanks rows. Then in my opinion the problem would be solved.


Sub Exporteren()

Sheets("Sheet 2").Select
Sheets("Sheet 2").Copy
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A11").Select


' Here I want to ad a VBA code to delete the contents if the criteria " " is met. then below VBA should work


ActiveSheet.UsedRange.Select
'Deletes the entire row within the selection if the ENTIRE row contains no data.
Dim i As Long
'Turn off calculation and screenupdating to speed up the macro.
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
'Work backwards because we are deleting rows.
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Selection.Rows(i).EntireRow.Delete
End If
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True


End With
End Sub

Please Login or Register  to view this content.

works for me..

Works for me too Thank you very much. I would have never come up with that.

Originally Posted by ragulduy

Please Login or Register  to view this content.

works for me..