Using .xlUp but disregarding formulas in cells

[davedave24]

Hi. I have a problem using Selection.End(xlUp) - I need it to disregard cells with formulas in.

I've tried to substitute for a Do-While loop but it has the same effect (albeit with a different outcome)

    Do Until ActiveCell.Value = ""
       ActiveCell.Offset(-1, 0).Select
    Loop

 

[davedave24]

Edit: I copied the wrong do-until loop up there

 

[SkipVought]

hi,

Do Until ActiveCell.Value = ""
    if not ActiveCell.Offset(-1, 0).HasFormula then
       'this is NOT a formula
    end if
    Activecell.offset(1).select
Loop

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I really don't like using Select and Activate, but sometimes it is required by the process.

Here's how I would code this, that puts each literal value in the range in the adjacent column for example...

    Dim r As Range
    
    For Each r In Range(Selection.End(xlUp), Selection)
        With r
            If Not .HasFormula Then
                .Offset(0, 1).Value = .Value
            End If
        End With
    Next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}