Range End(xlDown) method not good when only one row

[alwayslrN]

I have two workbooks which use the following code. The problem is it is possible at some point for one or both of the files to only have a header row and one row of data. In the case of the of the latter the End(xlDown) method will go throw every row in the spreadsheet. How can I avoid that, but still have the code work with there is more than one row after the header?

For Each CurtCellRng In Range(Range("K2"), Range("K2").End(xlDown))
       If CurtCellRng.Value = 0 Then
             CurtCellRng.Value = Cells(CurtCellRng.Row, "I").Value
             Cells(CurtCellRng.Row, "I").Value = 0
       Else
             CurtCellRng.Value = 0
       End If
    Next CurtCellRng

 

[SkipVought]

Hi,

I often use something like this...

    If Range("K2").CurrentRegion.Rows.Count > 1 Then
        For Each CurtCellRng In Range(Range("K2"), Range("K2").End(xlDown))
               If CurtCellRng.Value = 0 Then
                     CurtCellRng.Value = Cells(CurtCellRng.Row, "I").Value
                     Cells(CurtCellRng.Row, "I").Value = 0
               Else
                     CurtCellRng.Value = 0
               End If
        Next CurtCellRng
    End If

Skip,

_[red][/red]

 

[xlbo]

alternatively:

For Each CurtCellRng In Range(Range("K2"), Range("K65536").End(xlUP))


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question