CODEWITHSUNDEEP
vbaexcel
Sourabh
Sourabh

Reputation: 9

Treating Blank values in Excel using VBA code

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:-

  1. 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.
  2. 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.

Upvotes: 0

Views: 1339

Answers (2)

YowE3K
YowE3K

Reputation: 23974

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.

Upvotes: 1

Ioannis
Ioannis

Reputation: 5388

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:

Input sheet

Sheet2 before: Sheet2 Before

Sheet2 After: enter image description here

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).

Upvotes: 0

Related Questions