Solved

Skip first row in an Excel routine

Posted on 2014-02-04
4
495 Views
Last Modified: 2014-02-04
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
0
Comment
Question by:dbfromnewjersey
4 Comments
 
LVL 22

Assisted Solution

by:Flyster
Flyster earned 125 total points
ID: 39833187
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
 
LVL 35

Accepted Solution

by:
mvidas earned 125 total points
ID: 39833205
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
 

Author Comment

by:dbfromnewjersey
ID: 39833210
Thanks.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39833511
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now