How to run a code if in range has non blank cell?

Question

I got A1:D17 Range. If in my range has blank cells my code works but vice versa it doesn't work.

Please help to find out problem.

And I wrote this code:

Sub Example
    Workbooks("Training_VBA Codes").Activate
    Worksheets("Practice 4").Activate
    Range("A1").Select

    If ActiveCell.CurrentRegion.SpecialCells(xlCellTypeBlanks) = False Then

        ActiveCell.CurrentRegion.SpecialCells(xlCellTypeBlanks).Value = 0
        Range(ActiveCell, ActiveCell.End(xlDown).End(xlToRight)).Copy

    Else

        Range(ActiveCell, ActiveCell.End(xlDown).End(xlToRight)).Copy

    End If
End Sub

score 2 · Accepted Answer · answered Nov 29 '17 at 13:36

When you are working with .SpecialCells, you have to be careful. It there is no match found you will get an error.

Try it like this (UNTESTED)

Sub Sample()
    Dim rng As Range

    On Error Resume Next
    Set rng = Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0

    If Not rng Is Nothing Then '<~~ Check if there were blank cells found
        rng.Value = 0
        '
        '~~> Rest of the code
        '        
    End If
End Sub

Tip: Also avoid the use of .Activate/.Select. You may want to see How to avoid using Select in Excel VBA

score 1 · Answer 2 · answered Nov 29 '17 at 13:53

You shouldn't have to worry about whether there are empty cells or not. If you are trying to copy a UsedRange you could do something as simple as this:

AtiveSheet.Range("$A$1:" & ActiveSheet.UsedRange.Address).copy

That will copy every cell in the active sheet whether it's empty or not in the UsedRange which means if you only have one cell populated at d20 it will copy everything from A1:D20.

Also I think you might have your if statement backwards I think your if condition should be:

If ActiveCell.CurrentRegion.SpecialCells(xlCellTypeBlanks) = True Then

If it finds blank cells then set their value.

R.Roe, thank your answer. But I didn't just copy. I need to find blank cells on range if I have and put something on those blank cells. The code which I wrote works when I have blank cells. But if on range has no blank it gives error. I try to solve it. — Khazar, Nov 30 '17 at 06:31

How to run a code if in range has non blank cell?

2 Answers2