CODEWITHSUNDEEP
excelvba
Tuấn Anh Nguyễn
Tuấn Anh Nguyễn

Reputation: 1

How to leave the blank cells blank in excel VBA?

Here is my code for a button that replace the value in the selected range in excel,

Private Sub CommandButton1_Click()
Dim a As Variant
Dim b As Variant
b = "1"
Dim example, cell As Range
Set example = Range("G5:AK5,g7:ak7,g9:ak9,g11:ak11,g13:ak13,g15:ak15,g17:ak17,g19:ak19,g21:ak21,g23:ak23,g25:ak25,g27:ak27,g29:ak29,g31:ak31,g33:ak33,g35:ak35,g37:ak37,g39:ak39,g39:ak39,g41:ak41,g43:ak43,g45:ak45,g47:ak47,g49:ak49,g51:ak51")
For Each cell In example
If 1 < cell.Value < 8 Then cell.Value = b Else If cell.Value = vbNullString 
Then cell.Value = " "
Next cell
End Sub

How to make it not fill the blank cells in the selected range with the specificied value ? i only know basic coding, thanks you guys in advance !

Upvotes: 0

Views: 1213

Answers (2)

user4039065
user4039065

Reputation:

BTW, you had two g39:ak39 ranges in your range definition and example was dimmed as a variant, not specifically as a range.

Perhaps this would be a more concise range definition.

...
Dim example As Range, cell As Range

Set example = Range("G5:AK5")
for i=7 to 51 step 2
    Set example = union(example, intersect(range("G:AK"), rows(i)))
next i

debug.print example.address(0,0)
'G5:AK5,G7:AK7,G9:AK9,G11:AK11,G13:AK13,G15:AK15,G17:AK17,G19:AK19,G21:AK21,G23:AK23,G25:AK25,G27:AK27,G29:AK29,G31:AK31,G33:AK33,G35:AK35,G37:AK37,G39:AK39,G41:AK41,G43:AK43,G45:AK45,G47:AK47,G49:AK49,G51:AK51
...

Upvotes: 0

TechnoDabbler
TechnoDabbler

Reputation: 1275

As @Marcucciboy2 commented. There are a couple of syntax errors. You don't need to specifically check for the blanks (unless you're trying to do something else?). If the value in the cell is > 1 and < 8 then the cell value is set back to the value of variable b ...

Private Sub CommandButton1_Click()

    Dim b As Variant
    b = "1"

    Dim example, cell As Range
    Set example = Range("G5:AK5,g7:ak7,g9:ak9,g11:ak11,g13:ak13,g15:ak15,g17:ak17,g19:ak19,g21:ak21,g23:ak23,g25:ak25,g27:ak27,g29:ak29,g31:ak31,g33:ak33,g35:ak35,g37:ak37,g39:ak39,g39:ak39,g41:ak41,g43:ak43,g45:ak45,g47:ak47,g49:ak49,g51:ak51")

    For Each cell In example
        If cell.Value > 1 And cell.Value < 8 Then
            cell.Value = b
        End If
    Next cell
End Sub

Upvotes: 1

Related Questions