Treating Blank values in Excel using VBA code

Question

I am new to Excel VBA. I have made a macro-enabled Excel to record survey responses. Whenever I run the macro, certain cells get copied (from Sheet1) and stored as a row vector in another worksheet (Sheet2).

The problem is that, if Responder1 has some blank cells in his response, then when I record another response (Responder2), then values corresponding to the variable where Responder1 had blanks, are stored in the previous row.

Here is the VBA code

Sub Submit1()
Range("A2:C2").Select    #in Sheet1
Selection.Copy
Sheets("Sheet2").Select
Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End Submit1

Now there are two things that I can think of doing:-

If it is possible to check that if previous row has at least one non-blank entry then the new response will be recorded in next row automatically.
If above is not possible, can we assign some value for e.g. NULL or 0 to those blank cells, so that the new response can be stored in new row.

Your current code should be copying data to the first row following the last non-empty cell in column A. All copied cells will be placed **on that one row**, irrespective of whether column B or C was empty in the previous row. Your only issue would arise if column A itself was empty for Responder1, in which case Responder2's values would overwrite all of Responder1's values - but they would still be being written to a single row. — YowE3K, May 10 '17 at 19:59
@YowE3K Thanks for editing the question. Actually, I have to copy multiple range of cells. So I am doing it by rewriting the same code for each range of cells. So it's just not column A, but the column for multiple ranges. Also, i do not want any overwriting, I want new response to start in a new row. Do you have any suggestion for option no. 2? — Sourabh, May 11 '17 at 05:34
@Ioannis using following code : `Set multipleRange = Union(range1, range2, range3, range4) multipleRange.Select Selection.Copy Sheets("Raw Data").Select Worksheets("Raw Data").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial` But `Selection.Copy` is not working in this case. Don't know what is the problem. — Sourabh, May 11 '17 at 07:01
Have you defined `range1, ...range4` properly? are you familiar with [VBA debugging](https://www.google.nl/search?q=vba+debugging&oq=vba+debugging&aqs=chrome..69i57j0l5.2567j0j7&sourceid=chrome&ie=UTF-8)? — Ioannis, May 11 '17 at 07:05

score 1 · Accepted Answer · edited Sep 21 '17 at 15:43

Try the following code:

Sub Submit1()
    Dim nextRow As Long
    With Worksheets("Sheet2")
        'Find the last non-empty cell in the worksheet, and determine its row
        'Then add 1 to that, so we are pointing at the next row
        nextRow = .Cells.Find(What:="*", _
                              After:=.Range("A1"), _
                              Lookat:=xlPart, _
                              LookIn:=xlFormulas, _
                              SearchOrder:=xlByRows, _
                              SearchDirection:=xlPrevious, _
                              MatchCase:=False).Row + 1
        'Copy values to "nextRow"
        Worksheets("Sheet1").Range("A2:C2").Copy .Cells(nextRow, "A")
        'Perform other copies as necessary, e.g.
        Worksheets("Sheet1").Range("A5:D5").Copy .Cells(nextRow, "D")
        Worksheets("Sheet1").Range("X4:Z4").Copy .Cells(nextRow, "H")
    End With
End Sub

Regarding your second suggestion: Just don't do it.

Thanks. This code worked out for me. This is actually running a loop and finding out that if there is at least one non-blank entry in one row, then the next response will stored in next row automatically. Thanks for the help!! — Sourabh, May 11 '17 at 12:54

score 0 · Answer 2 · edited May 08 '18 at 21:49

Quick answer:

Use Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial SkipBlanks = False

Reference from MSDN:

SkipBlanks True to have blank cells in the range on the Clipboard not be pasted into the destination range. The default value is False.

However, using Select is not considered good practice. Consider reading this for more information. For copy-pasting ranges, I cannot recommend more Chip Pearson's page.

Demo:

Sub test()

Dim LastRow As Long Dim arCopy() As Variant 
Dim rDest As Range

With Sheet2 ' --> Qualify the ranges
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 End With

arCopy = Sheet1.Range("A2:K2").Value

Set rDest = Sheet2.Cells(LastRow, "A")

Set rDest = rDest.Resize(1, UBound(arCopy, 2))

rDest.Value = arCopy

End Sub

Sheet1:

Sheet2 before:

Sheet2 After:

The above has the added benefit that you do not need to worry about Blank cells, as they are copied by default.

I hope this helps!

EDIT (addressing comments)

It is true that since SkipBlanks = False by default, your PasteSpecial does not skip blanks. Are you sure your cells are indeed blank and do not appear to be blank? You can make a quick check with the ISBLANK() function. If SkipBlanks appears to be working only some times, then there is certainly something different with respect to the cells it is applied to.
With ... End With: This is a shortcut construct that enhances readability. Basically, a block of methods or properties that are under the same object, such as
```
 Sheet1.Range("A1")="Rob"
 Sheet1.Copy("A2")
 Sheet1.Rows.Count
```
can be written as
```
With Sheet1
    .Range("A1") = "Rob"
    .Copy("A2")
    .Rows.Count
End With
```
This enhances readability
Your second suggestion

can we assign some value for e.g. NULL or 0 to those blank cells, so that the new response can be stored in new row.

In principle, this is possible. However, you should identify which cells are "blank", and we already know that one method that does not skip blanks does not appear to work, so identifying blank cells and substituting them is a bit of a catch 22. In other words, if we knew how to find these "blank" cells so that we assign them NULL or 0, then SkipBlanks would had taken care of them in a more elegant way (because it is designed to do exactly this).

The "_SkipBlanks = False_" seems to have worked with some ranges (I am having different ranges in workbook, so repeating the code separately for each range). However I am having little difficulty in understanding your code here, specially "_with --- End with_". Also, Do you have any suggestion to option 2? — Sourabh, May 10 '17 at 19:51
The default value for the `SkipBlanks` parameter is `False` (as seen in the documentation that you included), so saying `SkipBlanks:=False` isn't necessary. — YowE3K, May 10 '17 at 20:01

Treating Blank values in Excel using VBA code

2 Answers2