Selecting non blank cells in a column

[utoyo]

Is there a way in excel to select a portion of a column (eg 10 consecutive cells) and have all the non blank cells selected. For example, I'd like to select rows 3-99 of the B column and then have all the rows with non blank cells selected?

If this isn't a native function of Excel, is there a way to have a macro set up to do it?

Thanks in Advance.

 

[combo]

Select range, Edit>Go to, and Special... Select features you like to select.
There is no way to select formulas and constants in one go, however it can be combined in macro:

Dim rng1 As Range, rng2 As Range
On Error Resume Next
With Selection
    Set rng1 = .SpecialCells(xlCellTypeConstants)
    Set rng2 = .SpecialCells(xlCellTypeFormulas)
    If rng1 Is Nothing Then Set rng1 = rng2
    If rng2 Is Nothing Then Set rng2 = rng1
    Union(rng1, rng2).Select
End With

combo

 

[hext2003]

Select your Range B3:B99

Press F5
Click on Special (at bottom)
Click on Blanks
Click OK

If you want to Fill all of these Blanks with a Value?

Type the Value you want and Press Cntl + Enter

This will populate all selected cells

 

[NWBeaver]

For non blanks it looks like you can do what hext2003 suggests, except select "Constants" rather than blanks.

 

[AnotherHiggins]

NWBeaver: As combo pointed out, that won't select cells containing formulas.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.