Skip first row in an Excel routine

The first row in my worksheet contains headers, so I don't want to apply the logic below to it.  How do I modify the code to skip the first row?  Thanks.

For Each oCell In Intersect(Range("B:Z"), ActiveSheet.UsedRange)
       'Set value of blank cells to 0
       If oCell.Value = "" Then
          oCell.Value = 0
       End If
Next
dbfromnewjerseyAsked:
Who is Participating?
 
mvidasCommented:
You could also just add another argument to the Intersect statement so you could keep the same "only used area" logic.  It would also be a good idea to check to make sure the intersect area exists, to avoid possible future errors.
Dim oCell As Range
If Not Intersect(Range("B2:Z100"), ActiveSheet.UsedRange, Rows("2:" & Rows.Count)) Is Nothing Then
    For Each oCell In Intersect(Range("B2:Z100"), ActiveSheet.UsedRange)
       'Set value of blank cells to 0
       If oCell.Value = "" Then
          oCell.Value = 0
       End If
    Next
End If

Open in new window

Alternately, you could just set all blank cells to 0 using SpecialCells:
Dim oCell As Range
On Error Resume Next
Set oCell = Intersect(Range("B:Z"), ActiveSheet.UsedRange, Rows("2:" & Rows.Count)).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not oCell Is Nothing Then oCell.Value = 0

Open in new window

Matt
0
 
FlysterCommented:
Set the range to start at row 2. You'll need to add a ending row, just make sure it's at or beyond the used range:

Dim oCell As Range
For Each oCell In Intersect(Range("B2:Z100"), ActiveSheet.UsedRange)
       'Set value of blank cells to 0
       If oCell.Value = "" Then
          oCell.Value = 0
       End If
Next

Flyster
0
 
dbfromnewjerseyAuthor Commented:
Thanks.
0
 
Saqib Husain, SyedEngineerCommented:
or even


For Each oCell In Intersect(Range("B:Z"), ActiveSheet.UsedRange)
if oCell.row >1 then
       'Set value of blank cells to 0
       If oCell.Value = "" Then
          oCell.Value = 0
       End If
endif
Next
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.